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.
- 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?
