We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Find row of calculated value

asked 2020-08-05 17:40:45 +0200

ajgringo619 gravatar image

I am using the MAXIFS function to find the newest date of a specific transaction, like this:

C:\fakepath\investments-calc.jpg

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-08-05 18:47:41 +0200

keme gravatar image

updated 2020-08-05 18:56:38 +0200

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.

edit flag offensive delete link more

Comments

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)),"")

ajgringo619 gravatar imageajgringo619 ( 2020-08-05 21:54:59 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-05 17:40:45 +0200

Seen: 56 times

Last updated: Aug 05 '20