To return multiple value using VLOOKUP function

[aalokwar]

Hi,
I got stuck when trying to check a matching value in multiple rows using VLOOKUP(...) function.
I have attached excel file for reference. Please solve it as soon as possible. Its urgent.

[tarquinious]

The problem with VLOOKUP is that it expects data to be sorted in Ascending order to work. Your formula will work if you sort the data in Columns A and then B of your lookup range.

[Cutter]

Try using this formula for D9 and down

=IF(COUNTIF($A$2:$A$6,B9),IF(COUNTIFS($A$2:$A$6,B9,$B$2:$B$6,"Blue"),"Y","N"),"NF")

[tarquinious]

Actually my solution above is incorrect. I am guessing you are eventually wanting to return the Price for each, so this is what the following does for you.

If you add in another column between B and C with the formula:
=CONCATENATE(A2,B2)
...copied down for all values in the table

Then for cell D9 (under the heading of Check Red) put in the formula:
=IF(ISERROR(VLOOKUP(B9&"Red",$C$2:$D$6,2,FALSE)),"NF",VLOOKUP(B9&"Red",$C$2:$D$6,2,FALSE))

Now you can copy that down for all Check Red items, and copy across (fixing the formula) for the Check Blue column.

Finally you can hide the new column C with the CONCATENATEs.