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

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