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

netrixuser

New Member
Joined
Jan 21, 2019
Messages
27
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
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
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:
Solution

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Jan 21, 2019
Messages
27
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
Joined
Jan 21, 2019
Messages
27

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 21, 2019
Messages
27

ADVERTISEMENT

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
Joined
Jan 21, 2019
Messages
27
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
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 21, 2019
Messages
27
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top