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(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

[dan]

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