Thread: Using VLOOKUP after finding LARGE value

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" <cowguy@aol.com> wrote in message
news:1153938170.142198.159420@m79g2000cwm.googlegroups.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

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" <cowguy@aol.com> wrote in message
news:1153939790.372570.287950@i3g2000cwc.googlegroups.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" <cowguy@aol.com> wrote in message
news:1153946150.373540.231790@m79g2000cwm.googlegroups.com...
>
> Pierre wrote:
>> Roger Govier wrote:
>
>
> Roger, thanks for the reply. I took a closer look. Works great!
>
> Pierre
>