Formula Help!

[lavita17]

Hi! I need help with creating a formula. I need to find out if the amount payed in Sheet1 found in column B, is seen in Sheet2. But there is a criteria, the lookup range should be either same date or up to 3 days after. Also I need to vlookup with the same criteria.

Example
Worksheet1
ColumnA          ColumnB               ColumnC         ColumnD
Date                 Amount Paid         Bank Recon     Date Recon
1-Oct-2020      20,000                    TRUE               3-Oct-2020

Worksheet2
Column A         ColumnB
Date Recon      Amount Paid
1-Oct-2020      15,000
2-Oct-2020      17,000
3-Oct-2020      20,000

I need your help in getting the formula for the 2 cells that are in bold and red format. Thank you!

Thank you!

[#2]

@lavita17 In case you are a MS365 subscriber and your Excel version supports the new FILTER function, the attached (rough) example might work for you.

[#3 · Accepted solution]

Hi @lavita17 

Please see the attached Excel file that might be helpful, if you are still facing issue add more data with different variation to get you the correct formula. With the current scenario it can be managed with INDEX+MATCH

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer and like it to help the other members find it more.

[#4]

Thank you so much this is a big help! I used IF + ISNUMBER(SEARCH) + VLOOKUP functions just to complete that data. Thank you again

[#5]

Thank you so much for your help! Unfortunately, I don't have the filter function. I better research this version. Thank you again