VLOOKUP - US values brought back as empty UK values

I'm having some difficultly with a vlookup.

I have a table that contains a lot of our customer information for both the UK & US. On my report I need to flag a column as "Y" if a customer is Diverse (this will only ever apply to US customers).

However when I do a simple VLOOKUP if CUSTOMER #1 has a UK record before a US one, it pulls this across incorrectly (blank instead of "Y").

I.e. I have this table:

Customer | Country | Diverse

A1| Edge | UK | N
A2| Edge | US | Y
A3| Smiths | UK | N


My report needs to show this

Customer | Spend | Diverse
A1| Edge | 100 | Y
A2| Smiths | 67 | N


If i put: =IF(VLOOKUP('Report'!A1,'Lookup'$1:$1048576,2,0)="US","Y","") it returns the cell as blank.

Eventually I need to do some more complex formulas later on but how can I get my VLOOKUP to register that there is a US line and return a "Y" value? Also, I can't just build upon the other table as it is the look-up table for other pages on the report.

Thanks
Alan

Try this

=IF(ISNUMBER(MATCH('Report'!A1&"*US*",INDEX('Lookup'!$A$1:$A$14&$B$1:$B$14,),0)), "Y","")