Lookup without VLOOKUP?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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.
 
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
 
Hi!

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

This will increment the col_index_num as you drag copy across.

Biff
 
Back
Top