Help with Index/Match + if blank

Jon R

Hello,

I am trying to create a formula for one sheet to search another sheet based on a PO#, and return the value of the latest PO revision. I'm trying to make this work without adding a helper column to the sample data sheet.

Here is sample data:

The result I want in a separate sheet:

I came up with two possible solutions but can't either to work.

1. Search for the PO number, pull value from the right most non-blank "Rev" cell or from PO value cell if all are empty. I'm trying to make this work on the first example using the following code but getting #incorrectargument.

=INDEX(IF(ISBLANK({PO Tracker Range 1}), {PO Tracker Range 3}, {PO Tracker Range 1}), MATCH([Qmerit PO Number]@row, {PO Tracker Range 4}, 0))

PO Tracker Range 1 is "Rev1" column

Range 3 is "PO Value" column

Rage 4 is "PO#" column from the data sheet

The idea is to find the PO#, check if Rev1 is empty, if so, return PO value, if not return Rev1 value. If this worked I would've nested inside of another if(isblank() to search Rev2 first.

The if(isblank() seems to work because if i take the approach of adding a helper column, I'm able to pull the right most/current PO value, and then do an index/match to pull this value into the second sheet. I need to avoid this approach because I can't add a helper column or change the format of the data sheet. Is the issue that Index cannot support a function as it's first argument or is my code incorrect?

2. Store the PO#, values, and issue dates as 3 cell arrays, find the most current date, return the cell with the corresponding value.

Not really sure where to start with this approach, seems like it's overly complicated. How do I look up/evaluate the latest date across three columns, then return the information in the cell to the left of that column?

Paul Newcome

Doing this without a hidden helper column in the source sheet requires a pretty lengthy formula, but if you can't add a hidden helper column, you would end up with something along the lines of this (using the three amount columns shown as an example - would need to continue the logic for additional columns):

=IF(IFERROR(INDEX({Rev 2}, MATCH([PO #]@row, {PO #}, 0)), "") <> "", INDEX({Rev 2}, MATCH([PO #]@row, {PO #}, 0)), IF(IFERROR(INDEX({Rev 1}, MATCH([PO #]@row, {PO #}, 0)), "") <> "", INDEX({Rev 1}, MATCH([PO #]@row, {PO #}, 0)), IF(IFERROR(INDEX({PO Value}, MATCH([PO #]@row, {PO #}, 0)), "") <> "", INDEX({PO Value}, MATCH([PO #]@row, {PO #}, 0)))))

Paul Newcome

The initial

"")

is the output of the IFERROR. If there isn't a match with the INDEX/MATCH, the IFERROR outputs a blank.

<> ""

means "is not blank", so if there is a match and the cell it pulls over is not blank

That final comma in your last question is the transition between the logical argument of the IF statement and the output if true.

The formula essentially says "If there is a match for the {Re 2} column and it is not blank then output that, otherwise check the {Rev 1} column. If that has a non-blank match, output that, otherwise check the {PO Value} column and use the same logic.

Paul Newcome

Doing this without a hidden helper column in the source sheet requires a pretty lengthy formula, but if you can't add a hidden helper column, you would end up with something along the lines of this (using the three amount columns shown as an example - would need to continue the logic for additional columns):

=IF(IFERROR(INDEX({Rev 2}, MATCH([PO #]@row, {PO #}, 0)), "") <> "", INDEX({Rev 2}, MATCH([PO #]@row, {PO #}, 0)), IF(IFERROR(INDEX({Rev 1}, MATCH([PO #]@row, {PO #}, 0)), "") <> "", INDEX({Rev 1}, MATCH([PO #]@row, {PO #}, 0)), IF(IFERROR(INDEX({PO Value}, MATCH([PO #]@row, {PO #}, 0)), "") <> "", INDEX({PO Value}, MATCH([PO #]@row, {PO #}, 0)))))

Jon R

Thank you for the reply.

Please explain the "") <> " ", section of the code.

Paul Newcome

The initial

"")

is the output of the IFERROR. If there isn't a match with the INDEX/MATCH, the IFERROR outputs a blank.

<> ""

means "is not blank", so if there is a match and the cell it pulls over is not blank

That final comma in your last question is the transition between the logical argument of the IF statement and the output if true.

The formula essentially says "If there is a match for the {Re 2} column and it is not blank then output that, otherwise check the {Rev 1} column. If that has a non-blank match, output that, otherwise check the {PO Value} column and use the same logic.

Jon R

Great, thanks so much for the answer and explanation. I'm able to get this working in a test scenario, going to try to implement for the real task.

Paul Newcome

Happy to help. 👍️