Can anyone help write excel nested if statement formula

[Sashimiandhisthunderpaws]

I'm really stumped trying to fix/write a formula and I'm not having any luck on Google/Youtube. I'm hoping that there is someone that can help me.

I'm trying to use a nested if statement to flag if multiple conditions are true. So first column is supplier and the second is cost centre. Our cost centre format is ####/####, eg 5000/5050. So I want to flag specified suppliers and cost centre combinations, eg supplier Asda and the cost centre either starting with 5999/, 6999 or 7.

Does anyone know how to do this? I've been messing around with search and find, wildcards.

Our cost centre format is ####/####, eg 5000/5050. So I want to flag specified suppliers and cost centre combinations, eg supplier Asda and the cost centre either starting with 5999/, 6999 or 7.

Based on your screenshot
Assuming column B contains supplier eg Asada
Column C is cost centre
Start on row 6

=if(And(b6="Asada",c6="5999/6999"),"Yes",if(And(b6="Asada,left(c6,1)=7),"yes","no")

You might need to put "7" if the left formula doesn't pick it up - it should but will depend on how your data is formatted.

Instead of having different columns, you can do all the exclusions in the one formula by adding more IF statements. Nested IFs work left to right so move along until one is true or all fail.

I would definitely recommend getting to grips with xlookup over if/ands.

I've been a nested if/and user for years, discovered xlookup a while ago and haven't looked back. It's so much better. Neater and less complicated once you've learnt it.

Sorry misread your cost centres - amended formula

=if(And(b6="Asada",left(c6,4)=5999),"Yes",if(And(b6="Asada",c6=6999),"Yes"if(And(b6="Asada,left(c6,1)=7),"yes","no")

Test each statement individually by breaking it up:
=if(And(b6="Asada",left(c6,4)=5999),"Yes","No")

As I noted above, you might find you need to put "5999", it will depend on your data formatting.

I found xlookups started long delays by commencing functions / calculating - I can't quite remember the wording. They are excellent and you can use multiple criteria etc. For 2000 rows they probably won't be too bad but 500k or more they killed my computer.

Thank you. I'm going to take a look at these in the morning.

Some of the cost centres don't have numbers after the /. Maybe I could convert to an 8 digit number then I could use <>= etc.

Sorry, i missed a comma, corrected

=if(And(b6="Asada",left(c6,4)=5999),"Yes",if(And(b6="Asada",c6=6999),"Yes",if(And(b6="Asada",left(c6,1)=7),"yes","no")

@k1233 Thanks. I'll give that a go tomorrow.

Thirded. Much safer. You can set each individual criterion in a separate column to display 1 or 0 and then multiply together to identify the items which tick every box on an AND basic or add them together to work as an OR.

Also a big fan of new XLOOKUP but I'm not sure it's the best solution here.

Apologies, I'm typing on the phone - wish we could edit!

=if(And(b6="Asada",left(c6,4)=5999),"Yes",if(And(b6="Asada",left(c6,4)=6999),"Yes",if(And(b6="Asada",left(c6,1)=7),"yes","no")

You don't need to convert to a number and match. The text functions LEFT, RIGHT, MID let you pull parts of numbers out of a string.

Left(c6,4) means go to cell c6 and then give me the first 4 digits.

If you want the last 4 digits, it's Right(c6,4)

Mid looks in the middle of the string. With 5999/5050 if you wanted 99/50 it would be MID(c6,3,5) Which means start at the 3rd character in the string and give me 5 characters.

@k1233 Yes you're right. I don't know why I didn't think of these - I use them often enough. I was fixated on wildcards today.

@EvilRingahBitch @titchy @foobio I saw that on a course as best practice but I've taken no notice, clearly. There are so many new formulas to get to grips with and spill errors.

Thanks to everyone for their help.

One other top tip, XLOOKUP handles wildcards all by itself, as long as you enter 2 for the search mode. This can simplify formulae a lot as you then don't need to embed LEN/LEFT/RIGHT/FIND/SEARCH functions in your lookup as you used to with VLOOKUP and INDEX/MATCH
Decent explanation here
exceljet.net/formula/xlookup-wildcard-match-example

Here's the formula to the first column. Hopefuly you can use it to write the others (would do them too but it's annoying on a phone and my baby is about to wake up 😂)

=IF(AND(A4="asda",OR(LEFT(B4,4)="5999",LEFT(B4,4)="6999",LEFT(B4,1)=7)),1,0)

Will display a 1 if condition met, 0 if not.

Sorry the =7 should be ="7" unless the cell is a number in which case none of the tests should have "" around them.. The way I copied it to excel on my phone in came over as text

What language are you all speaking? 🤣🤣🤣