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.

 

[Fluff]

Can you please explain in words what you are trying to do.

Also what version of Excel are you using?

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’)

 

[Alex Blakenburg]

Ideally answer Fluff's request for more information but as a shot in the dark.

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

Note: it will only get to the IFNA part if M3<>"" AND O3<>""
It seems a bit odd to be checking for O3 = "" AFTER you have possibly already used it if M3=""

 

[mohdabrar]

So this is what I am trying to do. I have data in M Column, which I want to VLOOKUP from Sponsor List Ref Sheet. However, sometimes, the M column is empty and I have data in O Column. and in Some cases both M and O column is empty in which case, There's nothing to LOOKUP. In a scenario where both M and O Column are empty I want the formula to get the data which is in cell R. Else, just VLOOKUP column M. Hope its clear. I am using Office 365.

 

[Fluff]

What is meant to happen if M3 is empty?
Also If you use R3 for the lookup, what range should it be looking in?

 

[mohdabrar]

What is meant to happen if M3 is empty?
Also If you use R3 for the lookup, what range should it be looking in?

The formula goes in "N" Column. If M3 is empty, It should give me the value from O3. However, if both M3 and O3 are empty, I need value from R Column. Else, I just want to VLOOKUP M column in Column N from Sponsor List Ref. Sheet.

 

[Fluff]

I'm sorry but this is not making any sense to me.
At the moment you have a formula that is looking up M3 in col B of the Sponsor List Ref sheet & if that returns #N/A it lookups M3 in col F.
Now you are saying that i M3 is not blank it should lookup M3 in col N

 

[Fluff]

At a guess, is this what you want

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

 

[Sufiyan97]

Try

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

 

[Alex Blakenburg]

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

“Some cases both M and O column is empty in which case, There's nothing to LOOKUP.”

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