How to lookup a second or third value ia a table array against a lookup value

[uniquevignesh]

Hi everbody,
Can some one help me with solution to lookup a second or third value ia a table array against a lookup value.
Example: In a below table, Vlookup we can get the value for bike as "5000", but I need to lookup the secong value for Bike, which is - 4500 & third value & so on, if available. Plz help me!

Model Value
Bike 5000
Car 15000
Truck 25000
Bike 4500
Truck 50000

[cbatrody]

Hi,

Try the following formula in E2:

=IFERROR(INDEX($B$1:$B$13,SMALL(IF($A$2:$A$13=$D2,ROW($A$2:$A$13)),COLUMN(A1))),"")

array entered (CTRL+SHIFT+ENTER)

See the attached file.

[Czeslaw]

Option function aggregate

[RedSummer]

Screen Shot 2015-08-28 at 11.14.40 AM.png

Made from both cbatrody and Czeslaw's great answers.

The Aggregate option is simple and shows from Largest to smallest, but blanks are listed as 0, (add a IF 0 then blank on the formula).

The Index small option shows the numbers in the order they appear, and leaves blanks where there aren't values.

Red

[Czeslaw]

Please Login or Register  to view this content.

[uniquevignesh]

Thank to all. Got the solution...

[Tony Valko]

Option function aggregate

Note that the AGGREGATE function requires Excel 2010 or later.