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.

Could you use a VLookup for this, I have to Google it every time but I think you could use it for this

Try Googling iferror(vlookups….

Have you got a screenshot of the excel sheet? Im guessing you've tried IF and AND statements combined?

Use AND with IF to combine conditions
like this
=IF(AND(Something is True, Something else is True), Value if True, Value if False)

Does Countifs function help?

Using a vlookup & concatenating the other 2 fields might let you have a single look up table for the combinations possible. I agree this is likely to be far easier than a complicated set of of nested IF statements.

Insert a column which concats the supplier & cost centre
pivot table to get a list of unique combinations
next to pivot table entries, put the true/false statements (assuming there's more than one option that could be true/false)
vlookup back to pivot table (+TF) to insert TF option in the active table

I have to copy & paste vlookup syntax every time, it's not as intuitive as I'd like

I may be misunderstanding what you're trying to do but I taught myself how to only count entries if multiple conditions were true.
This was using COUNTIFS rather than COUNTIF , and in the example I was working on, I wanted to count only if [as an example] A1-A500 was "Yes", and F1-F500 was "Green".
Is that the sort of thing you're wanting to do, or am I barking up the wrong tree?

I would post on the excel sub of reddit. I’ve done that before and some clever boff came along and just wrote the formula for me, which was good as I would never have been able to sort it myself!

When learning excel, don't jump straight to nesting formulae, use separate columns to do each stage of the calculation one at a time. Once you've got to a column with your answer, you can then (in another column) nest together the steps. (Or just hide the steps and keep the final column).

Although if you are setting a flag for each row in a table I would have thought "if" combined with "And" and "Or" functions would do the job.

It's an IF and AND statement.

IF this is ASDA AND the cost centre is 123, then THIS, else ....

But if you have lots of cost centres and lots of suppliers, then it gets way more complicated.

How many combinations do you have?

I haven't tried vlookup/iserror. I've started with if, and, or, search. Will try to screen shot with the formula shown. I'll show

I'm trying to get the cost centre check to work before i insert into the if statement.

This!!! You end up with missing brackets somewhere otherwise. Far easier to do complex nested formula in section.

Screenshot and I will help

Best practice is xlookup over vlookup these days, folks. Get with it

If you can post an example we should be able to help, I find If combined with AND and often need iserror but would.need context to.write it for.you

Agree, xlookup is way better than vlookup. And much easier for people to do - no need for true/false at the end, or counting the number of columns. Just =xlookup(lookup value, search column, result column)

If you have a lot of data, this is going to be clunky to process in Excel. It might be better to consider a database, even a csv or excel file exported into Access would be more robust. Failing that, a pivot table would be better than rows and rows of nested calculations.

Loads. Its an automated template to create a PO commitment accrual from a PO report. We receive consolidated invoices from some suppliers and these are picked up in a separate accruals process, but only if its for central cost centres or the generic cost centre for that business segment. Individual invoices are also issued to specific sites which we would need to pick up.

I'd probably:
Bump the table down so there's a blank row 1 above the field headers in row 2
In your first blank column eg C, type in C1 the first supplier name you're searching and in C2 the first code prefix you're searching
In C3:
=If(And(A3=C$1,Left(B3,len(c$2))=C$2),"Y","N")
Fill down column 3 from C3 to end of table
Add other supplier/prefix combos similarly in rows 1&2 in new columns.
Fill right from c3 across all columns
Fill down in new columns from row 3

You can then use auto filter to find Ys in a specific column, or add a column at the end searching for Ys in any column eg cell Z3
=if (countif ("Y",C3:Y3)>0,"Matched","Not Matched")

Not tested as I'm in bed.

Though lljjkk's solution above will look neater. But maybe harder to maintain, and to do if you're not a pivot tabler.

How about a pivot table?

Not sure if I can enlarge. This is a simplified version.

My report is nearly 2000 lines. I have 20 different checks flag as include/exclude so that I filter these to be picked up.

Thanks for all the suggestions. I'm working my way through.

@TheNoseKnows I've done something similar linking to a cell a cell rather than entering the names and numbers into the formula.