Trying to use IF and IF(AND to return values based on three different values that an XLOOKUP provides

[netrixuser]

Hi all,
I am trying to use IF statements to enter data into a cell based on an XLOOKUP result.
Thus far I have the following IF and IF(AND but although I do not get a syntax error, it isn't fully working.

=IFNA(IF(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-8 Speakers","8",IF(AND(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-16 Speakers",XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-T4M Speakers"),"16","0")),"")

Cell M16 is a unique ref number, Column Users!F:F contains a list of those unique ref numbers
Column Users!Z:Z will either contain 0.1-8 Speakers, 0.1-16 Speakers or 0.1-T4M Speakers
Using the IF and IF(AND, I would like to place the following:
For 0.1-8 Speakers place an 8
For 0.1-16 Speakers place a 16
For 0.1-T4M Speakers place a 0

With the formula I pasted above the 8 and 0 work but if the XLOOKUP returns 0.1-16 Speakers I still get a 0 (expecting a 16)

Hopefully I have missed something simple ! or maybe I'm approaching it the wrong way ?
All help gratefully received - such a learning curve, but enjoyable !!

Regards

Netrix

 

[jasonb75]

You need to remove the AND function from the formula, the XLOOKUP part can not return 2 different results at the same time so the result will always be FALSE and the whole formula will return 0.

Also, you don't need the third IF, once the first 2 possibilities have been checked, anything that is not an error must be the third possible answer by process of elimination.

=IFNA(IF(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-8 Speakers",8,IF(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-16 Speakers",16,0)),"")

Final tip, when entering numbers into formulas, use 16 not "16". Entering numbers in double inverted commas makes them text which can cause problems later.

 

[Saurabhj]

Hi Netrix,

Why you are using AND ?

=IFNA(IF(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-8 Speakers","8",IF(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-16 Speakers","16","0")),"")

will give the correct result. Please check if it helps.

Thanks,
Saurabh

 

[netrixuser]

You need to remove the AND function from the formula, the XLOOKUP part can not return 2 different results at the same time so the result will always be FALSE and the whole formula will return 0.

Also, you don't need the third IF, once the first 2 possibilities have been checked, anything that is not an error must be the third possible answer by process of elimination.

=IFNA(IF(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-8 Speakers",8,IF(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-16 Speakers",16,0)),"")

Final tip, when entering numbers into formulas, use 16 not "16". Entering numbers in double inverted commas makes them text which can cause problems later.

Thanks for the speedy reply Jason - works a treat !!

 

[netrixuser]

Hi Netrix,

Why you are using AND ?

=IFNA(IF(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-8 Speakers","8",IF(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z)="0.1-16 Speakers","16","0")),"")

will give the correct result. Please check if it helps.

Thanks,
Saurabh

Thank you Saurabhj, I wish I knew why I decided to use AND !! as I spent a lot of time trying to get this working
Your code works, but Jasonb75 beat you to the answer
Great tip from Jason too re entering numbers with quote marks around them.

 

[jasonb75]

There are a few other ways that you could do it as well, first one that comes to mind, although I should point out that this is an untested idea. I've used similar methods before but not with XLOOKUP.

=IFNA(XLOOKUP(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z),"0.1-"&{8,16,"T4M"}&" Speakers",{8,16,0})),"")

 

[netrixuser]

There are a few other ways that you could do it as well, first one that comes to mind, although I should point out that this is an untested idea. I've used similar methods before but not with XLOOKUP.

=IFNA(XLOOKUP(XLOOKUP(M16,Users!$F:$F,Users!$Z:$Z),"0.1-"&{8,16,"T4M"}&" Speakers",{8,16,0})),"")

Oh wow - might have a play with that later. I'm loving the simplicity that XLOOKUP can be, I'd only just got my head around Index/Match and thought that was a revelation compared to VLOOKUP !
I struggle with how to "word" and IF and an XLOOKUP - as I said in my OP, its a learning curve for sure !!
Currently, with the code below, it places a yes or no dependent on the XLOOKUP:

=IFNA(IF(XLOOKUP(C16,Users!$F:$F,Users!$AA:$AA)="0.21 Headset + 1 Handset","Yes","No"),"")

I would like to modify it to not return anything if C16 is blank, I tried this:

=IF(C16=" "," ",IF(XLOOKUP(C16,Users!$F:$F,Users!$AA:$AA)="0.21 Headset + 1 Handset","Yes","No"))

But I still get a "No" if C16 is Blank
[IFNA removed for now - I usually add that in when everything else works!]

 

[netrixuser]

Oh wow - might have a play with that later. I'm loving the simplicity that XLOOKUP can be, I'd only just got my head around Index/Match and thought that was a revelation compared to VLOOKUP !
I struggle with how to "word" and IF and an XLOOKUP - as I said in my OP, its a learning curve for sure !!
Currently, with the code below, it places a yes or no dependent on the XLOOKUP:

=IFNA(IF(XLOOKUP(C16,Users!$F:$F,Users!$AA:$AA)="0.21 Headset + 1 Handset","Yes","No"),"")

I would like to modify it to not return anything if C16 is blank, I tried this:

=IF(C16=" "," ",IF(XLOOKUP(C16,Users!$F:$F,Users!$AA:$AA)="0.21 Headset + 1 Handset","Yes","No"))

But I still get a "No" if C16 is Blank
[IFNA removed for now - I usually add that in when everything else works!]

Happy to report I fixed this one myself

 

[jasonb75]

Normally for a blank you would use "" instead if " " (like having quotes around numbers, having a space between them to represent a blank is a bad idea that will come back and haunt you.

Happy to report I fixed this one myself

I don't think that it's a formal requirement, but it is always appreciated that if you are able to solve your own problem that you post how you did it as an answer to your own question so that anyone else with a similar problem looking at your thread may benefit from it

 

[netrixuser]

Happy to report I fixed this one myself

Really appreciate the help I'm getting here - for completeness, the way I fixed my issue:
To try and diagnose my problem, I took this line

=IF(C16=" "," ",IF(XLOOKUP(C16,Users!$F:$F,Users!$AA:$AA)="0.21 Headset + 1 Handset","Yes","No"))

And removed the IF elements to reveal that the XLOOKUP was returning a 0 and not a blank cell.
I amended my If statement to be

=IF(C16=0," ",IF(XLOOKUP(C16,Users!$F:$F,Users!$AA:$AA)="0.21 Headset + 1 Handset","Yes","No"))

And all is good in the world ! (for now!!)
I have since taken advice and changed the If statement to read IF(C16=0,"", blah blah (ie removed the actual space from within the quotes.
Thanks again,

Netrix