Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to see columns from which values were not returned by HLOOKUP functions? | Excel Worksheet Functions | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) |