Forum Discussion
Janedb
Apr 20, 2023Iron Contributor
Use cell value to return date value
Hi all, I am trying to return a date as soon as the value of a cell equals 0. This is for a loan account to return the expiry date of the loan as soon as the value is 0.
If value in H16:H103=0, then use cells A16:A103 to get the expiry date
6 Replies
Sort By
- NikolinoDEGold Contributor
You can use the IF function in Excel to return the expiry date from column A when the value in column H is equal to 0.
Here’s an example of how you could do this:
=IF(H16=0,A16,"")
This formula uses the IF function to check if the value in cell H16 is equal to 0. If it is, the formula returns the value from cell A16 (the expiry date). If the value in cell H16 is not equal to 0, the formula returns an empty string ("").
You can then copy this formula down to cells A17:A103 to apply it to the rest of the rows in your data.
I hope this helps!
- JanedbIron Contributor
The IF function will not work in this instance, I tried it.
I actually need to have the expiry date at the top of the spreadsheet in one cell, so it must lookup up the value in column H16:H103 and find the expiry date in A16:A103 and then return the first date when the zero balance appears- NikolinoDEGold Contributor
In that case, you can use a combination of the INDEX and MATCH functions to achieve this. The INDEX function returns the value of a cell in a given range based on its position, while the MATCH function searches for a value in a range and returns its relative position.
Here’s an example formula that you can use to find the first expiry date in column A when the balance in column H is 0:
=INDEX(A16:A103,MATCH(0,H16:H103,0))
This formula uses the MATCH function to search for the value 0 in the range H16:H103. The third argument of the MATCH function is set to 0 to find an exact match. The MATCH function returns the relative position of the first cell in the range H16:H103 that contains the value 0.
The INDEX function then uses this relative position to return the corresponding expiry date from the range A16:A103.
You can enter this formula in a cell at the top of your spreadsheet to display the first expiry date when the balance in column H is 0.
I hope this helps!