Keep cell empty unless condition is met

[RodneyW]

I'm using this formula =VLOOKUP($C$5,Data!$D$2:$E$123,2,FALSE) in cell E5. I need to modify this so that if cell D5 is empty, cell E5 will also be empty. Only if cell D5 has content, will the formula kick in and display a value.

Thanks in advance

 

[plettieri]

Hi RodneyW,

This might work using an if statement first

=IF(D5="", "", VLOOKUP($C$5, Data!$D$2:$E$123, 2, FALSE))


Also X lookup would work

=IF(D5="", "", XLOOKUP($C$5, Data!$D$2:$D$123, Data!$E$2:$E$123, ""))

hope this helps
plettieri

 

[Fluff]

Xlookup will not work as the OP's profile clearly shows 2013, which does not have xlookup.

 

[plettieri]

yes you are correct...I. missed the excel version indicated by the OP

plettieri

 

[RodneyW]

Hi RodneyW,

This might work using an if statement first

=IF(D5="", "", VLOOKUP($C$5, Data!$D$2:$E$123, 2, FALSE))


Also X lookup would work

=IF(D5="", "", XLOOKUP($C$5, Data!$D$2:$D$123, Data!$E$2:$E$123, ""))

hope this helps
plettieri

Thanks for the help. I can get the first to work but it exposes another issue. I describe the issue after the first paragraph below but the paragraph directly below has what may be an alternative solution.
====
I may be able to use my original formula =VLOOKUP($C$5,Data!$D$2:$E$123,2,FALSE) but putting conditional formatting on cell E5 saying if D5 is empty, color the font this color. Something along the lines of =IF($D$5,""). Any ideas?
====

The exposed issue---- If Calculation Options are turned to automatic, it works perfectly. I have Calculation options turned to MANUAL because cell C5 has this formula.

=INDEX(Data!$D$3:$D$122,RANDBETWEEN(1,COUNTA(Data!$D$3:$D$122)))

The form I'm creating needs to allow a user to enter a value in D5 (3 character airport code such as LAX) that is their answer to "what is the airport code for the city shown in C5". If calculation options are turned to automatic, the value in C5 changes every time anything is done in another cell, including the value in C5. If I change calculation options to Manual, then C5 doesn't change until I hit F9, which forces the calculations to take place. When calculations are set to manual, neither of your formulas work.

Thanks for your continued help.

 

[Fluff]

Both work

Does that mean you are no longer using 2013?

For the CF you can use

=D$5$=""

and set the format how you wish.

 

[RodneyW]

Does that mean you are no longer using 2013?

For the CF you can use

=D$5$=""

and set the format how you wish.

Fluff, you're CF code solved the problem. Note, I edited the response above, I was moving too quick and posted a incorrect response. Xlookup didn't work. I am still using 2023.

THANKS ALL FOR THE HELP

 

[Fluff]

Glad to help & thanks for the feedback.