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)


--
Regards

Roger Govier


"Pierre" wrote in message
oups.com...
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

Roger Govier wrote:
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)


--
Regards

Roger Govier



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.

--
Regards

Roger Govier


"Pierre" wrote in message
ups.com...

Roger Govier wrote:
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)


--
Regards

Roger Govier



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

Pierre wrote:
Roger Govier wrote:


Roger, thanks for the reply. I took a closer look. Works great!

Pierre

You're welcome. Thanks for the feedback

--
Regards

Roger Govier


"Pierre" wrote in message
oups.com...

Pierre wrote:
Roger Govier wrote:


Roger, thanks for the reply. I took a closer look. Works great!

Pierre