Lookup without VLOOKUP?
 

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.

Lookup without VLOOKUP?
 

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.

Lookup without VLOOKUP?
 

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.

Lookup without VLOOKUP?
 

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

Lookup without VLOOKUP?
 

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

Lookup without VLOOKUP?
 

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

Lookup without VLOOKUP?
 

thanks guys! works great!