IF with VLOOKUP formula not returning specific text

[asnary]

This formula works:
=VLOOKUP('Quote Notes'!B4,'[ml_customer list plus.xlsx]Customer List +'!A4:Z2001,21,FALSE)

Column 21 has some cells that are empty and in those instances I would like the return to read "Please provide"

I have tried:
=IFERROR(VLOOKUP ....) but realized this isn't an error, its just empty
~ and ~
=IF(VLOOKUP('Quote Notes'!B4,'[ml_customer list plus.xlsx]Customer List +'!A4:Z2001,21,FALSE)="'',"Please provide")
which does not work
~ and ~ a number of other combinations

I have ensured that the cells in column 21 are in fact empty and that the destination cell is formatted to receive text ('General')

Any ideas?

 

[Joe4]

Welcome to the Board!

One way is to use this structure:

=IF(VLOOKUP('Quote Notes'!B4,'[ml_customer list plus.xlsx]Customer List +'!A4:Z2001,21,FALSE)="","Please provide",VLOOKUP('Quote Notes'!B4,'[ml_customer list plus.xlsx]Customer List +'!A4:Z2001,21,FALSE))

 

[asnary]

Welcome to the Board!

One way is to use this structure:

=IF(VLOOKUP('Quote Notes'!B4,'[ml_customer list plus.xlsx]Customer List +'!A4:Z2001,21,FALSE)="","Please provide",VLOOKUP('Quote Notes'!B4,'[ml_customer list plus.xlsx]Customer List +'!A4:Z2001,21,FALSE))

Joe4 thank you so much! I invested about 3 hrs on my own investigating and just couldn't find the right combo. This works. Many thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

A more compact structure that you could also use with your 365 version is

=LET(r,'[ml_customer list plus.xlsx]Customer List +'!A4:Z2001,v,INDEX(FILTER(r,INDEX(r,0,1)='Quote Notes'!B4,""),1,21),IF(v="","Please provide",v))

 

[Joe4]

Joe4 thank you so much! I invested about 3 hrs on my own investigating and just couldn't find the right combo. This works. Many thanks!

You are welcome.

The structure if just this:
=IF(VLOOKUP(...)="","Please Provide","VLOOKUP(...))
Basically you are saving if the formula returns a blank, return something, otherwise, return the value from the formula.
So you are having to repeat the formula twice.

Peter's method avoids having to do that, but I admit I have not played around with the new LET function yet.