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

#### netrixuser

##### New Member
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.

Excel Formula:
``=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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### jasonb75

##### Well-known Member
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.
Excel Formula:
``=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.

Last edited:

#### Saurabhj

##### Active Member
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

##### New Member
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.
Excel Formula:
``=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

##### New Member

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
Great tip from Jason too re entering numbers with quote marks around them.

#### jasonb75

##### Well-known Member
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.
Excel Formula:
``=IFNA(XLOOKUP(XLOOKUP(M16,Users!\$F:\$F,Users!\$Z:\$Z),"0.1-"&{8,16,"T4M"}&" Speakers",{8,16,0})),"")``

#### netrixuser

##### New Member

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.
Excel Formula:
``=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:
Excel Formula:
``=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:
Excel Formula:
``=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

##### New Member
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:
Excel Formula:
``=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:
Excel Formula:
``=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

##### Well-known Member
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

##### New Member
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
Excel Formula:
``=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
Excel Formula:
``=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

Replies
3
Views
136
Replies
1
Views
77
Replies
7
Views
61
Replies
8
Views
87
Replies
3
Views
135

1,127,594
Messages
5,625,695
Members
416,127
Latest member
MALEPINZON

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back