need help with IF in excel 2016

ok so I have a spreadsheet which has 3 different names in a drop down list well i want different values based on the name selected while if for the selected name is blank its value is "" but also doesn't interfere with the other names on the list.... here is what i have so far

=IFS(
NAME="John Doe",John_GAS_SATURDAY_INITIAL,
NAME="Jane Doe",Jane_GAS_SATURDAY_INITIAL,
NAME="Jean Doe",Jean_GAS_SATURDAY_INITIAL)

ideal results is

John Doe Gas is empty return ""
Jane Doe gas is $5.00 return "$5.00"
Jean Doe gas is $6.00 return "$6.00"

previous attempts would return "" for the selected name even when giving the value because gas for the 1st name tested was empty...

any help would be appreciated!

Hi tm, welcome to Excel Forums! Will this work?:
=CHOOSE(MATCH(NAME,{"John Doe","Jane Doe","Jean Doe"},0),John_GAS_SATURDAY_INITIAL,Jane_GAS_SATURDAY_INITIAL,Jean_GAS_SATURDAY_INITIAL)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

anyway to make it display an empty cell rather than $0.00 when there isn't a value assigned to the names?

Actually, use this custom Number Format if you want to display currency amounts or blanks: $0.00;$0.00;;

...OR here's a formula that works. NOD to Ford for the more efficient Find method. I put his formula in an INDIRECT function to generate a Defined Name instead of text, then used the returned value as a divisor. Zeros cause an error, which yields "" through the IFERROR clause.

=IFERROR(1/(1/INDIRECT(LEFT(NAME,FIND(" ",NAME)-1)&"_Gas_Saturday_Initial")),"")

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

the last formula didn't work so here is a link to my Excel file so that you can see what I'm working with

https://www.dropbox.com/s/vhhghgoae6...heet.xlsm?dl=0

the file does have macros so if you see a warning it's just a macro to create a folder with the next Saturday's date then save as pdf

here it is!

Originally Posted by leelnich

Perhaps it didn't work because you hadn't defined the name Jean_Gas_Saturday_Initial! When I did that and applied the formula with Name = "Jean Doe", it worked fine.

ps. Ford, I think the formula goes in SAT!$H$29

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

well those names where generic names and the defined names was for the real names... srry forgot to change the defined names.. b10 for each name tab goes in h29 Saturday depending on the name selected

We ALL miss details like that at one point or another, it's half the reason why this forum exists! So, is your problem solved? Does the formula work with your actual data?

how do i just keep it simple... like

if name = "John Doe" do ISBLANKelse if name = "Jane Doe" do ISBLANK else if name = "Jean Doe" do ISBLANK

=IF(NAME="John Doe",IF(ISBLANK(JOHN_GAS_SATURDAY_INITIAL),"",JOHN_GAS_SATURDAY_INITIAL),)
IF(NAME="Jane Doe",IF(ISBLANK(JANE_GAS_SATURDAY_INITIAL),"",JANE_GAS_SATURDAY_INITIAL),)
IF(NAME="Jean Doe",IF(ISBLANK(JEAN_GAS_SATURDAY_INITIAL),"",JEAN_GAS_SATURDAY_INITIAL),)

tried this and it doesn't work results in #VALUE error!

well, i think I finally figured it out!

=IFS(
NAME="John Doe",IF(ISBLANK(JOHN_GAS_SATURDAY_INITIAL),"",JOHN_GAS_SATURDAY_INITIAL),
NAME="Jane Doe",IF(ISBLANK(JANE_GAS_SATURDAY_INITIAL),"",JANE_GAS_SATURDAY_INITIAL),
NAME="Jean Doe",IF(ISBLANK(JEAN_GAS_SATURDAY_INITIAL),"",JEAN_GAS_SATURDAY_INITIAL))