Using VLOOKUP after finding LARGE value

  • Thread starter Thread starter Pierre
  • Start date Start date
P

Pierre

This string returns #N/A:

=VLOOKUP(LARGE('Price List'!B3:B44,1),'Price List'!$C$3:$H$44,6,FALSE)

First need to identify the LARGEST value(in this case the most recent
date) then bring in corresponding data such as the next price-breaks.

To find the 2nd most recent date and its price: the next line, would
read:

=VLOOKUP(LARGE('Price List'!B3:B44,2),'Price List'!$C$3:$H$44,6,FALSE)

Its apparent I cannot do this in a single cell.

TIA for any ideas.

Pierre
 
Hi Pierre

Isn't the problem that you should be using the same column for both
functions??

=VLOOKUP(LARGE('Price List'!$C$3:$C$44,1),'Price
List'!$C$3:$H$44,6,FALSE)

If you wanted the formula to be copied down and automatically
incremented to the next largest value, you could use

=VLOOKUP(LARGE('Price List'!$C$3:$C$44,ROW(1:1)),'Price
List'!$C$3:$H$44,6,FALSE)
 
Roger said:
Hi Pierre

Isn't the problem that you should be using the same column for both
functions??

=VLOOKUP(LARGE('Price List'!$C$3:$C$44,1),'Price
List'!$C$3:$H$44,6,FALSE)

If you wanted the formula to be copied down and automatically
incremented to the next largest value, you could use

=VLOOKUP(LARGE('Price List'!$C$3:$C$44,ROW(1:1)),'Price
List'!$C$3:$H$44,6,FALSE)
Click to expand...
Roger, I'm using the LARGE function in column C to identify the proper
row, and the resulting answer to retrieve some data 6 rows over
(beginning in the same column), or am I missing something.

Thanks for responding.
Pierre
 
Hi Pierre

Because you said you were getting #N/A errors, I just wondered if you
were inadvertently using column B for your Large function, but column C
to try to find the result of the large function.
If column B and Column C contain dates, do they both have the same date
values?
Maybe, the value of the largest date in column B, doesn't exist in
column C.

Alternatively, of course, the value may be being found in column C, but
there is no associated value in the same row in column H.

My sue of the ROW() function, to change form largest to second largest
etc as you copy down the column will work, providing you do have data in
the relevant columns.
 
Back
Top