software

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(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-Proquot;dan48quot; wrote:

gt; In doing a hlookup I would like returned the cell contents from the left
gt; column and also left of that as in the example
gt;
gt; x y z aa ab ac
gt; 1 2 3 4 5 6
gt;
gt; hlookup(ab1,a1:dd1000, 1) = 5
gt; but I am interested in receiving answer 4 and separetely answer 3
gt;
gt; Dan

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

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; hlookup(ab1,a1:dd1000, 1) = 5
gt;
gt; for the matched item:
gt; =INDEX(A1D1000,1,MATCH(AB1,A1D1,0))
gt;
gt; For the item one cell to the left of the matched item:
gt; =INDEX(A1D1000,1,MATCH(AB1,A1D1,0)-1)
gt;
gt; For the item two cells to the left of the matched item:
gt; =INDEX(A1D1000,1,MATCH(AB1,A1D1,0)-2)
gt;
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;dan48quot; wrote:
gt;
gt; gt; In doing a hlookup I would like returned the cell contents from the left
gt; gt; column and also left of that as in the example
gt; gt;
gt; gt; x y z aa ab ac
gt; gt; 1 2 3 4 5 6
gt; gt;
gt; gt; hlookup(ab1,a1:dd1000, 1) = 5
gt; gt; but I am interested in receiving answer 4 and separetely answer 3
gt; gt;
gt; gt; Dan