IF and MATCH formula combined?

[consulttk]

Someone please help! I have 3 columns, A, B, and C. I'm trying to build a formula to search column B for a certain value "NO". If "NO" shows up anywhere in column B, then I want it to return "NO" and list the associated values in column A and C. If "NO" isn't found at all, then it should just return the value "YES".

I think this would be a combo IF/MATCH statement, but I have no idea where to begin, and how to make it create a list of return values if "NO" shows up more than once. I attached a workbook which I think better explains what I'm trying to accomplish, any help is MUCH appreciated!

Book1.xlsx

[DBY]

Hello
If you wish to do this with formulas, then you could use Array formulas or formulas with helper column. Take a look at my attached reply for examples of each approach. If you're using a lot of Array formulas they can be resource hungry, so use sparingly.

Hope this helps.
DBY

[consulttk]

Book1_Reply(2).xlsxDBY, thanks for your quick reply! I'm going to be using this for a summary sheet with a lot of data, so I think your non-array formula would be best. It works great in the example you created, but when I tried copying the formula into another example it's not returning any values. I'm having difficulty troubleshooting b/c I don't quite understand how the formula is working, and I want to make sure I can replicate this with the rest of my summary sheet, can you take a look I see what I did wrong?

Thanks again!

[DBY]

Hello
Yes, you've just missed a couple of things in the formulas you need to be aware of. Firstly in the helper column you need to return 1, 2, 3 etc. so subtract the number of rows above it in the formula. So it becomes:

=IF(C24="NO",ROW()-23,"")

Notice the minus 23 as your formula is on row 24.

Likewise the Small function within the Index function needs to return 1, 2, 3 etc and so you need the row part to reference a cell on the first row of the sheet. Your formula is now:

=IFERROR(INDEX($B$24:$B$26,SMALL($E$24:$E$26,ROW(A1))),"")

Notice the ROW(A1). As you drag the formula down, A1 becomes A2 and so on.

You have to be aware of where your data is positioned on the sheet. I've amended the file, take another look as see if it makes sense.

Just as an after thought, if you have a lot of data, have you thought of perhaps using a Pivot Table? You would then have no need for formulas at all.

DBY

[consulttk]

Got it, thank you sooo much!!!

[Marcol]

If you are uncomfortable with arrays, try this workbook.
Admittedly the formula is much longer than the array alternative, but it might just be faster on larger sheets.

[consulttk]

Thanks Marcol, this also does exactly what I need, now I just need to decipher how it works so I can keep using it!