Hlookup in other columns
 

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

Hlookup in other columns
 

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


"dan48" wrote:

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

Hlookup in other columns
 

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

"Ron Coderre" wrote:

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


"dan48" wrote:

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