Jul 23 2019 01:15 PM
In the attached file, I am looking for a formula in cell F18 that pulls in the latest (based on date) value for B18. For the latest date, if B18 is matched in Column B, then pull the value from Column H. If the B18 is matched with Column C, then pull the value from Column I.
In this specific example, the formula in F18 should pull the value form I10.
Jul 24 2019 02:44 AM
Try this formula in F18:
=IF(COUNTIF(C$2:C18,B18),
LOOKUP(2,1/(C$2:C18=B18),I$2:I18),
LOOKUP(9.9E+307,H$2:H18))
Jul 24 2019 07:50 AM
Jul 24 2019 09:37 AM
This works. However, there might be times when I need to the latest value for B18 may be in column B or Column C. How would I adjust this formula to look up either column?@ChrisMendoza
Jul 24 2019 11:56 AM
@inmanc -
You'll have to work out the logic, something along the lines of the attached. This should be a good start. I used structured references as it was getting really ugly really fast. Learn more about that at https://support.office.com/en-gb/article/using-structured-references-with-excel-tables-f5ed2452-2337...