Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - Return value of the cell below the formula's answer
I'm using VLOOKUP and the value being searched for in the table array covers
4 rows (ie header "April" - search value - is in merged cells A12:A15). So when I enter "return value in column 3(C)", it returns the value in the highest row - being cell G12. As there are 4 different entries for April (in cells B12, B13, B14 & B15), the lookup needs to return each of the B cells in different formulas. I can't move part of argument to column headers, as that is where my account manager names a hence return value in column 3(C). Other cells return values from columns 4 - 9 (D - I). Is there a way to add to the formula to return the value BELOW the one found with VLOOKUP? ie return value in cell R-1? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - Return value of the cell below the formula's answer
Maybe you can use =index(match()).
With the table in A:B of sheet2 and the value to match in A1: This will get the first match: =index(sheet2!b:b,match(a1,sheet2!a:a,0)) Second: =index(sheet2!b:b,match(a1,sheet2!a:a,0)+1) Third: =index(sheet2!b:b,match(a1,sheet2!a:a,0)+2) .... I didn't test it, but it should work ok if =match(a1,sheet2!a:a,0) returns the first row with the match with merged cells. Tinkerbell.1178 wrote: I'm using VLOOKUP and the value being searched for in the table array covers 4 rows (ie header "April" - search value - is in merged cells A12:A15). So when I enter "return value in column 3(C)", it returns the value in the highest row - being cell G12. As there are 4 different entries for April (in cells B12, B13, B14 & B15), the lookup needs to return each of the B cells in different formulas. I can't move part of argument to column headers, as that is where my account manager names a hence return value in column 3(C). Other cells return values from columns 4 - 9 (D - I). Is there a way to add to the formula to return the value BELOW the one found with VLOOKUP? ie return value in cell R-1? Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
displaying a formula's answer into a separate cell | Excel Worksheet Functions | |||
My formula's answer is calculated, but disappears | Excel Worksheet Functions | |||
Formula not Return Required Answer Q | Excel Worksheet Functions | |||
vlookup answer in different cell | Excel Worksheet Functions | |||
Return answer if..... | Excel Worksheet Functions |