If Then with Multiple Ifs

Ed B.

I have two columns that I need to check if "true" before returning the text in the 3rd column. I am trying to create a formula that returns the Awarded Contractor's name in each City, i.e. Contractor Y for Boise and Contractor A for Chicago. I have tried a number of different IF, IFS, IF(AND) statements and can't figure it out. Any help would be appreciated.

Nic Larsen

If I understand what two columns you're validating, try this:

=IF(AND(NOT(ISBLANK(City@row)), Awarded@row = 1), Contractor@row)

Ed B.

Thanks for replying. I think I may have left something out. I will have a column that has Boise on one row and Chicago on another and I want a formula next to each city that returns the awarded contractor for each respective city.

Nic Larsen

It sounds like you need something more like this:

=IFERROR(INDEX(COLLECT(Contractor:Contractor, City:City, [City for Contractor]@row, Awarded:Awarded, 1), 1), "")

What I don't know is the column you are referencing that has city you are matching if it is different than your City column. So I've put as "city for contractor". You would need to update that in the formula.

Ed B.

Nic, Thank you so much for following up. This almost worked. However, it only returns the first contractor listed with the matching City and does not take into account the Awarded checkbox. I tried changing the order to look at Awarded first and then City but that returned an Unparseable error.

Eric Law

Hello @Ed B. try this =INDEX(COLLECT(Contractor:Contractor, City:City, City@row, Awarded:Awarded, 1), 1)

Lol, that's the same as Nic's...

Ed B.

Eric, your formula actually worked. It was different from Nic's in that it did not include the IFFERROR command. I made that change and it worked beautifully. Thank you so much to you and Nic for getting me there. I appreciate it.