Conditional VLookup

[HNarli]

Hi,

I was wondering if someone could please help me with a Vlookup. I am creating a product bill of material and performing a Vlookup to each of the supplier parts which I have in another supplier spreadsheet.

This is working fine, however I have an issue when no match is recognised in that the cell is displays as blank. I wanted some way of conveying to the user that no match is found so I have managed to get it to display as XX to warn someone that nothing is found.

However the problem that I have is the ‘XX’ always appears by default which is not what I want. I have tried writing below what I am trying to achieve in a hope that someone can make sense of this and help! Thanks in advance!

IF there is a value in cell F2 then do this lookup =IFERROR(VLOOKUP(I2,[Tbl_Supplier_Costs.xlsx]Components!$B$1:$Q$99997,4,FALSE),"XX")
IF there is no value in cell F2 then do nothing / show a blank cell.

Thank you

 

[Fluff]

How about
=IF(F2="","",IFERROR(VLOOKUP(I2,[Tbl_Supplier_Costs.xlsx]Components!$B$1:$Q$99997,4,FALSE),"XX"))

 

[HNarli]

Hi, Thanks so much, it sort of does what I need. I changed it to say =IF(F2>=1,IFERROR(VLOOKUP(F2,[Tbl_Supplier_Costs.xlsx]Components!$B$1:$Q$99996,4,FALSE),"XX")) as it wasn't working with the above which sort of does what I need. It displays FALSE everywhere that is blank though which looks a bit unsightly, but at least it adds up now! Is there a way to remove the FALSE in blank cells please? Not to worry if not I'll just try and hide it with white font!
Thank you
P.S I love your name!!

 

[Fluff]

You don't have an argument for the false part of the IF, which is why you get false. Try
=IF(F2>=1,IFERROR(VLOOKUP(F2,[Tbl_Supplier_Costs.xlsx]Components!$B$1:$Q$99996,4,FALSE),"XX"),"")

 

[HNarli]

Oh now that is perfect! Thank you

 

[Fluff]

You're welcome & thanks for the feedback.