netrixuser
Board Regular
- Joined
- Jan 21, 2019
- Messages
- 77
- Office Version
- 365
- Platform
- Windows
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.
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
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