Need help index/match formula

[inmanc]

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.  

@Ashish Mathur 

[#2]

@inmanc 

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

[#3]

@inmanc -

Find the attached file.

[#4]

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 

[#5]

@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...