I am using the MAXIFS function to find the newest date of a specific transaction, like this:
=MAXIFS($A:$A,$B:$B,“Buy”,$C:$C,H3)
This works, returning the latest “Buy” transaction date of “ALGO-USD”. What I want to get is the last “Share Price” based on the date returned. Is there a way to get the row number of the datum returned by this function?
You can use the MATCH() function to find the row number. The INDEX() or OFFSET() function can be used to point to a specific cell in that row. Note that INDEX() assumes a position number (1-based, i.e. row 1 is the top row) while OFFSET() assumes a displacement (0-based, move 0 rows down to return the top row).
You can also in most cases use a lookup function directly without determining the row number first. LOOKUP() or VLOOKUP() may be your best solution. VLOOKUP() requires that the key (date) is to the left of the target column (price). LOOKUP() requires that the key column is sorted.
Thanks for this. I found that using INDIRECT/ADDRESS with MATCH was the ticket.
UPDATE: I ended up switching back to INDEX/MATCH:
=IFERROR(INDEX(Share_Range, MATCH(1, (“Buy”=Type_Range)*($H3=Asset_Range),0)),"")
