Vlookup Doesn't Recognise Identical Price

Hi all,

I've attached an example sheet,
which contains Product(s) / Name / Cost

Example: E2:E5 I require the 4 lowest prices

E2 =small(array,1)
E3 =small(array,2)
E4 =small(array,3)
E5 =small(array,4)

My problem is when I try to match names to the prices,
It doesn't recognise an identical price that belongs to a different product. (See F2 & F3)
Any help much appreciated, even a pointer in the right direction.

Regards,
Chad

For most LookUps, you need key field to be unique. VLOOKUP by default will return first exact match. Thus Name5 is repeated twice.

You need additional calculation to exclude already fetched item from Lookup.
Also, you can't use VLOOKUP alone to look from right to left (it uses left most column to find value and return value of column specified in argument for the same row).

Also shouldn't correct answer be...
$5.00 Name 1
$5.00 Name 14
$6.00 Name 6
$8.00 Name 12

With your set up, you can use something like...

=INDEX($B$2:$B$25,MATCH(0,COUNTIF($F$1:F1,$B$2:$B$25)+NOT(($C$2:$C$25=E2)),0))

Confirmed as array (CTRL + SHIFT + ENTER).

See attached.

Thank CK, I'll try implementing it now,
Yes mate you are correct, I manually entered lol (human error)
correct answer be...
$5.00 Name 1
$5.00 Name 14
$6.00 Name 6
$8.00 Name 12