IFS, IFNA and Vlookup

[mohdabrar]

Hi,

I am trying to get the below formula to work. I don't know what I am getting wrong over here. Can someone please help with the same.

=IFS(M3="",O3,O3="",R3),IFNA(VLOOKUP(M3,'Sponsor List Ref'!B2:C95,2,0),VLOOKUP(M3,'Sponsor List Ref'!$F$2:$G$6,2,0)))

The formula works well with only IFNA part, but if i add the IFS before, it gives a #Value! error. Any input is appreciated.

Thanks.

 

[mohdabrar]

I am unable to attach a screenshot. It says the file is too big. It's 266 KB.

Let me try another way to explain this. I have data in Column "M" and I am trying to VLOOKUP the data that matches column M from a different sheet. However, at certain intervals, there is no data in Column M. So when Column has data, it'll VLOOKUP from the other sheet. When it doesn't, I want data from O. When both M and O are Empty, I want data from Q. Hope the below table helps.


M N O P Q

Primary Bill Sponsor	Classification	Pri. Insurance Company	Pri. Network/Plan Type	Rate Plan
THIQA-DAMAN	As There's Data in "M" VLOOKUP from Sponsor List Ref Sheet	THIQA-ABU DHABI MINISTRY OF FINANCE	Daman Thiqa1	RUW-DAMAN THIQA
	As both "M" and "O" are empty I need data from "Q"		Daman Ehanced Sahtak - ADNOC	RUW-CASH
THIQA-DAMAN	Formula goes in this Column	THIQA-ABU DHABI MINISTRY OF FINANCE	Daman Thiqa1	RUW-DAMAN THIQA
DAMAN - NATIONAL HEALTH INSURANCE COMPANY		DAMAN - NATIONAL HEALTH INSURANCE COMPANY	Daman Ehanced Sahtak - ADNOC	RUW-DAMAN ADNOC
THIQA-DAMAN		THIQA-ABU DHABI MINISTRY OF FINANCE	Daman Thiqa1	RUW-DAMAN THIQA
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
	Here I need Data from "O"	THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
	As both "M" and "O" are empty I need data from "Q"		RUW - Daman ABM5	RUW - DAMAN ABM

 

[Fluff]

Have you tried any of the formulae that have been suggested?

 

[mohdabrar]

Also this quote implies that if M3 is blank that you want to use O3 in your vlookup.


Do you only ever want to use M3 in the vlookup ?

I only need VLOOKUP if there's data in M. If M3 is blank I just want what's in O3 in N and if both M3 and O3 are blank, I want what's in Q, as per the above table.

 

[Fluff]

That's what the formula in post#8 does.

 

[mohdabrar]

That's what the formula in post#8 does.

I tried almost all, Missed that one. It worked!!! The rest for some reason, kept giving 0 when the value is only in Q. I don't know what's that about. I tried to get the data from Q in a separate column to get rid of the blanks using IF, AND, OR but i keep getting 0. But Thanks this worked!

 

[Fluff]

Glad we could help & thanks for the feedback.

I would (to some extent) recommend against using the IFS function, as it's less efficient than nested IF functions.

 

[mohdabrar]

Thank you! I know I've marked this as Solved, but can you help with the other question here? I am using this formula here. But, It doesn't return the Rate Plan if Primary Bill Sponsor and Primary Insurance are empty. It gives me a 0. What am I doing wrong? =IF(OR(AND(M2="",P2="",S2),M2=""),P2,M2). Thanks in Advance.

Primary Bill Sponsor	Insurance	Pri. Insurance Company	Pri. Network/Plan Type	Rate Plan
THIQA-DAMAN	=IF(OR(AND(M46="",P46="",S46),M46=""),P46,M46)	THIQA-ABU DHABI MINISTRY OF FINANCE	Daman Thiqa1	RUW-DAMAN THIQA
			Daman Ehanced Sahtak - ADNOC	RUW-CASH
THIQA-DAMAN		THIQA-ABU DHABI MINISTRY OF FINANCE	Daman Thiqa1	RUW-DAMAN THIQA
DAMAN - NATIONAL HEALTH INSURANCE COMPANY		DAMAN - NATIONAL HEALTH INSURANCE COMPANY	Daman Ehanced Sahtak - ADNOC	RUW-DAMAN ADNOC
THIQA-DAMAN		THIQA-ABU DHABI MINISTRY OF FINANCE	Daman Thiqa1	RUW-DAMAN THIQA
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
DAMAN - MANDATE 5 - ABM FUNDED MANDATE ADMINISTRATION		THIQA-ABU DHABI MINISTRY OF FINANCE	RUW - Daman ABM5	RUW - DAMAN ABM
			RUW - Daman ABM5	RUW - DAMAN ABM

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks