VLOOKUP & non-contiguous table array
 

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,

VLOOKUP & non-contiguous table array
 

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
--
Gary''s Student - gsnu200800


"Ruchi A." wrote:

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,

VLOOKUP & non-contiguous table array
 

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,

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200800


"Ruchi A." wrote:

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,

VLOOKUP & non-contiguous table array
 

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

Try this:

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


--
Biff
Microsoft Excel MVP


"Ruchi A." wrote in message
...
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,

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200800


"Ruchi A." wrote:

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,