Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
displaying a formula's answer into a separate cell [email protected] Excel Worksheet Functions 6 September 26th 06 10:00 PM
My formula's answer is calculated, but disappears Niki Excel Worksheet Functions 4 September 11th 06 11:25 PM
Formula not Return Required Answer Q John Excel Worksheet Functions 1 April 23rd 06 09:42 AM
vlookup answer in different cell Psychlogic Excel Worksheet Functions 1 January 24th 06 10:40 PM
Return answer if..... Pat Excel Worksheet Functions 3 March 8th 05 11:52 PM


All times are GMT +1. The time now is 10:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"