Using VLOOKUP after finding LARGE value

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
 
R

Roger Govier

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)
 
P

Pierre

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
 
R

Roger Govier

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP - Not returning the proper number (Caro) 1
trouble with (complicated) vlookup searching another workbook 2
Find Last entry for Col in Multi column array using cell from another array. 1
need help doing a vlookup and average 7
Using Vlookup and IF statements to check for blank cells 4
using an IF or AND function to get next largest value in list 1
Excel Lookup 1
Sum Products with VLOOKUP 3

Top