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, t...
Forum Discussion
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
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!
It returns an error because the value is part of a formula, I changed your formula
=INDEX(A16:A103,MATCH(0,H16:H103,0)) to =INDEX(A16:A103,MATCH(0,H16:H103,-1))
It now returns a date, but the date is one month prior to the expiry dateThe third argument of the MATCH function specifies the match type.
A value of 0 means that the function will find the first exact match. A value of -1 means that the function will find the smallest value that is greater than or equal to the lookup value.
If you’re getting an error with the original formula, it could be because there is no exact match for 0 in the range H16:H103. In this case, using a match type of -1 will return the smallest value that is greater than or equal to 0, which may not be what you want.
If you want to find the first occurrence of 0 in the range H16:H103 and return the corresponding expiry date from the range A16:A103, you should use a match type of 0. If this returns an error, it means that there is no exact match for 0 in the range H16:H103.
If you’re getting a date that is prior to the expiry date with your modified formula, it could be because there is no exact match for 0 in the range H16:H103 and the MATCH function is returning the position of a value that is greater than 0.
Can you please check if there is an exact match for 0 in the range H16:H103?
NikolinoDE I attached a sample with the -1 it works