Hlookup in other columns

[dan48]

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

 

[Ron Coderre]

Try something like this:

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

for the matched item:
=INDEX(A1D1000,1,MATCH(AB1,A1D1,0))

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

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


Does that help?

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

XL2002, WinXP-Pro

 

[dan]

Your formula workes very well. Thank you for your help
Dan