Vlookup & IF Help

[paul_r26]

Hello all,

I hope you are having a nice day.

Please could you help me! In the below I would like to add in that if the vlookup finds "nights" to show nothing. Is that possible?

=VLOOKUP($B$2,INDIRECT($D6&"!$A:$K"),3,FALSE)

Many thanks

 

[Joe4]

What version of Excel are you using? I have a solution in mind, but it involves some of the newer functions available in newer versions of Excel.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

 

[Joe4]

I will just post both functions.

Assuming your original formula works, this should so what you want in all versions of Excel:

=IF(VLOOKUP($B$2,INDIRECT($D6&"!$A:$K"),3,FALSE)="nights","",VLOOKUP($B$2,INDIRECT($D6&"!$A:$K"),3,FALSE))

If you have Excel 365, you can use the new LET function to shorten it, i.e.

=LET(x,VLOOKUP($B$2,INDIRECT($D6&"!$A:$K"),3,FALSE),IF(x="nights","",x))

 

[paul_r26]

Apologies, is this it:

Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20496) 64-bit

 

[paul_r26]

Thanks so much!!

 

[Joe4]

You are welcome. So did that do what you need?

Apologies, is this it:

Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20496) 64-bit

If you update your Profile, as described in my first post, it will always show under your name, and you won't need to mention it in every thread.

It will show up like mine:

 

[paul_r26]

All done cheers