If / And with Vlookup not working.

MichaelO1

Hello,

I cannot seem to get the following to work:

=IF(AND({Explanatory Measures Range 5} = "1", {Explanatory Measures Range 1} = "03-01-01.01 EX - % potential School L/B"), VLOOKUP([Primary Column]1), {Explanatory Measures Range 6}, 4, false),"")

Kelly Moore

Hey @MichaelO1

I'm happy to help. One cannot use cross sheet references within IFs the same way as when the IF is in the same sheet. This being said, let's see if we can get to the result you need using an Index/Collect.

=IFERROR(INDEX(COLLECT({Explanatory Measures whatever column 4 was}, {Explanatory Measures whatever column 1 was}, [Primary Column]@row, {Explanatory Measures Range 5}, 1, {Explanatory Measures Range 1}, "03-01-01.01 EX - % potential School L/B"),1),"")

In the formula above, note where I refer to a single column, not the 4 column table you originally called out as Range 6. I didn't know if your Range 5 and Range 1 were single cells or entire columns. In the formula above, either will work.

The 1 at the end of the formula belongs to the INDEX function and says to use the first value found in this 'list'. You need this in the formula. If there are no matches to the criteria, the Index/Match would throw a 'No Match' error. The IFERROR takes care of this and will return a blank. This should be the equivalent to your IF/AND. I wasn't sure if you meant to specifically call out row 1 of the Primary column or if you meant the current row you were on. If you meant row1 specifically and only row 1, replace the @row designation with the number 1 as you had originally written.

Remember you must physically create the new cross sheet references in your sheet.

Does this work for you?

Kelly

MichaelO1

Hello, thank you for your response. Unfortunately, I could not get the formula to work.

I am trying to evaluate if A =1 and B = 03-01-01.01 EX - % potential School L/B then retrieve the value in C. In the image below A=1, B=03-01-01.01 EX - % potential School L/B so the result (C) would be 250.46.

What column would "Explanatory Measures whatever column 4 was" and "Explanatory Measures whatever column 1 was" be? IFERROR(INDEX(COLLECT({Explanatory Measures whatever column 4 was}, {Explanatory Measures whatever column 1 was}

Kelly Moore

Hey Michael01

To answer you question above. A VLOOKUP uses a table as it's range. According to your original formula your table had 4 columns. The INDEX/MATCH or INDEX/COLLECT can use singular columns instead of a table as the range. So referring to your VLOOKUP table - whatever column your column#4 within that table is what I meant. I didn't have the screenshot to help me identify that column.

To be clear of what you are trying to accomplishing -on a different sheet, I'll call it your main sheet, you have a Primary column that contains the same information as the {Strategy Explanation} on this screenshot sheet. This is what I'm assuming.

=IFERROR(INDEX(COLLECT({Explanatory Measures Outcome Measures}, {Explanatory Measures Strategy Explanation}, [Primary Column]@row, {Explanatory Measures Range 5}, 1, {Explanatory Measures Range 1}, "03-01-01.01 EX - % potential School L/B"),1),"")

Remember you have to physically create any new cross sheet references. You cannot simply copy paste this formula into your sheet.

As written, this should return the result when your criteria are met. The formula cell should be blank if the criteria are not found.

Does this work for you? If it doesn't, please tell me what is the result - unexpected result or error (what error?).

Kelly