i have two columns. i want to look through columA for value X and want to
return the value in columnB in the same row. i need to do this without
vlookup so i can make a recursive equation that i can just drag across the
whole spreadsheat.


A B
1 6
2 5
3 7
4 8

so if i were to serch for "4" in colA, i'd get back "8"

any ideas? thanks in advance.

Why doesn't

=VLOOKUP(4,A1:B1000,2,False)

work?

Otherwise, try

=INDEX(B:B,MATCH(4,A:A,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"J" wrote in message
...
i have two columns. i want to look through columA for value X and want to
return the value in columnB in the same row. i need to do this without
vlookup so i can make a recursive equation that i can just drag across the
whole spreadsheat.


A B
1 6
2 5
3 7
4 8

so if i were to serch for "4" in colA, i'd get back "8"

any ideas? thanks in advance.

The key may be using absolute references in the table_range in your vlookup:
=vlookup(4,$A$1:$B$1000,2,false). Those $ signs will keep the cell
references from changing as you drag the formula around the spreadsheet.

"J" wrote:

i have two columns. i want to look through columA for value X and want to
return the value in columnB in the same row. i need to do this without
vlookup so i can make a recursive equation that i can just drag across the
whole spreadsheat.


A B
1 6
2 5
3 7
4 8

so if i were to serch for "4" in colA, i'd get back "8"

any ideas? thanks in advance.

the problem is the 3rd entry of the vlookup, the return value. i want that
to move if the column is copied. so i was hoping on getting an equation in
colB that i could extend to colC, colD, ... without having to change the
return value every time. thanks for the responses and sorry for not being
specific.

~j

One method.....

Select B1:E1

Enter this in active cell(B1)

=VLOOKUP(lookup_value,Table,{2,3,4,5},FALSE)

Then hit CTRL + SHIFT + ENTER

The formula will be entered across the 4 cells with the third argument
incremented.


Gord Dibben Excel MVP


On Wed, 16 Nov 2005 12:11:54 -0800, "J" wrote:

the problem is the 3rd entry of the vlookup, the return value. i want that
to move if the column is copied. so i was hoping on getting an equation in
colB that i could extend to colC, colD, ... without having to change the
return value every time. thanks for the responses and sorry for not being
specific.

~j

Hi!

=VLOOKUP(4,$A$1:$B$100,COLUMNS($A:B),0)

This will increment the col_index_num as you drag copy across.

Biff

"J" wrote in message
...
the problem is the 3rd entry of the vlookup, the return value. i want
that
to move if the column is copied. so i was hoping on getting an equation in
colB that i could extend to colC, colD, ... without having to change the
return value every time. thanks for the responses and sorry for not being
specific.

~j

thanks guys! works great!