Match multiple Partial Text in one cell & return Max date in excel

0
votes

I am trying to match partial data from multiple cells and want Max date in return. Tried Max index match and search function but no luck. If anyone can help.

Example:

Sheet1- Column A: Row1- ahah_ah-ata_KH_079 to ahjhd_Baker Row2- ahah_ah-ata_KH_079 to ahjhd_Baker

Column B: Row1-12/5/2020 11:58:00 PM Row1-12/5/2020 11:31:00 PM

Sheet:2 Column A: Row1- KH_0278 Row2- KH_079 Row3- DUA001

Column B: Row1- KH_0275 Row2- Baker Row3- BBA001

Expected Return: In Sheet:2- Column C: Row1- NA Row2- 12/5/2020 11:58:00 PM Row3- NA

1
excelexcel-formula

1 Answers

0
votes

=IF(MAXIFS(Blad1!B$1:B$2,Blad1!A$1:A$2,"*"&A1&"*",Blad1!A$1:A$2,"*"&B1&"*")=0,"NA",MAXIFS(Blad1!B$1:B$2,Blad1!A$1:A$2,"*"&A1&"*",Blad1!A$1:A$2,"*"&B1&"*"))

Use this formula in Sheet2 cell C1. It checks the partial match of both A1 & B1 in the given range in Sheet1 column A and returns the maximum value of Sheet1 column B from the matching range of column A.

Since this formula returns 0 if there's no match I added the IF statement.