Hlookup in other columns

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

Guest

In doing a hlookup I would like returned the cell contents from the left
column and also left of that as in the example

x y z aa ab ac
1 2 3 4 5 6

hlookup(ab1,a1:dd1000, 1) = 5
but I am interested in receiving answer 4 and separetely answer 3

Dan
 
Try something like this:

hlookup(ab1,a1:dd1000, 1) = 5

for the matched item:
=INDEX(A1:DD1000,1,MATCH(AB1,A1:DD1,0))

For the item one cell to the left of the matched item:
=INDEX(A1:DD1000,1,MATCH(AB1,A1:DD1,0)-1)

For the item two cells to the left of the matched item:
=INDEX(A1:DD1000,1,MATCH(AB1,A1:DD1,0)-2)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Back
Top