VLOOKUP & non-contiguous table array

[Ruchi A.]

Hi,

I am trying to use the VLOOKUP feature an skip a column when I define my
table array in the VLOOKUP formula. Is that possible?

For example, I have data in A2:J10. Because of data & formulas I have in
column B through I, I don't want to include it in my table array in the
VLOOKUP formula.

Here is my current formula that gives me a circular reference error:
=vlookup($a$1,A2:J10,9)

However, I want to leave the data in column B through I and not move it to
another part of the worksheet.

I want to use only column A and J as my table array.


Is it possible to define a non-contiguous range of cells as the table array?

Of course I can move my data around, but I wanted to find if there was a
formulaic way to get what I need.

Thanks all,

 

[Gary''s Student]

I think your formula is working. Say A1 contains 123 and A4 contains the
same thing. VLOOKUP finds the value in A4 and then looks across to I4 and
gives the contents of I4

 

[Ruchi A.]

The formula returns a circular referrence error because A1 contains a formula
that refers to column D. I want to eliminate the circular reference.

Thanks,

 

[T. Valko]

=vlookup($a$1,A2:J10,9)

Try this:

=INDEX(I2:I10,MATCH(A1,A2:A10))