Using IF/AND with INDEX/MATCH

Susan Albers

Hello -

I have multiple sheets set up to feed into 1 result set and am trying to find a way to pull information from the result set into a table, if the criteria is met. Here is what I have, however, I keep getting #INVALID OPERATION.

=IF({BCOA Range 11} = "YES", AND({BCOA Range 10} = "LCB", INDEX({BCOA Range 12}, MATCH([Column3]1, {BCOA Banking Center #}, 0))))

Also, I will need to add on to this for another scenario as well -i.e. =IF({BCOA Range 11} = "YES", AND({BCOA Range 10} = "LTCF", INDEX({BCOA Range 12}, MATCH([Column3]1, {BCOA Banking Center #}, 0))))...

HELP! please.. :-)

Thank you in advance..

Susan Albers

Please ignore these questions, as I was able to find a solution that will meet my needs. Thank you.

David Joyeuse

Hi @Susan Albers

Not sure I follow what you intend to do, but there's many errors here:

1. In both your formulas I believe the AND is wrongly placed. I guess it's part of the criteria for the IF function, so it should be like this: =IF(AND(Criteria 1, Criteria 2, ...)
2. In both case you're trying to check the value of a full range. You cannot do that. {BCOA Range 11} cannot be compared to "YES". You have to reference a cell here, not a range. You can do that, but only in functions like COUNTIFS or SUMIFS to name a few.

All I can say for now :)

Susan Albers

Thank you @David Joyeuse -

Your feedback has helped, however, now I am receiving #INCORRECT ARGUMENT SET, using the following:

=IF(AND({LegacyCompany} = "LCB", {Self Audit} = "YES", INDEX({BCOA District #}, MATCH([Column3]1, {BCOA Banking Center #}, 0))))

Any insight would be greatly appreciated!

Thank you.

Susan Albers

UPDATED QUESTION:

I have 2 active forms on one sheet. These forms are being completed by separate groups. I need to pull the score from each group/form and display it on a summary sheet. I am running into trouble on this example:

It will find a match on the Banking Center #, and pull the responses for group 1

=IF([Self Audit Received]@row = "YES", INDEX({Score group 1}, MATCH([Banking Center #]@row, {BankingCenter#Range 3}, 0)))

However, there will be a second form that I need to pull from information from as well, and it is stopping once it finds a match. Is there a way to pull this information together into one form - or loop through the results set until it finds a value >0?

Susan Albers

Please ignore these questions, as I was able to find a solution that will meet my needs. Thank you.