Required for Excel formula suggestion - reg

Hi Satheshkumar,

For your requirement — where you want to check if a UTR number from the Master sheet is NOT available, then automatically check the 2nd sheet and produce the result — and since VLOOKUP cannot look to the left — I suggest using INDEX-MATCH or XLOOKUP (if your Excel supports it).

Here’s a formula approach using INDEX-MATCH:

Assuming:

• Master Sheet UTRs are in Sheet1!A2:A100

• Second Sheet UTRs are in Sheet2!A2:A100 and corresponding Supplier Names in Sheet2!B2:B100

• You want to find Supplier Name from Sheet2 if UTR from Master is missing in Master sheet

Step 1: Check if UTR from Master is missing in Sheet2

Step 2: Return Supplier Name from Sheet2 if found, blank if not found

Step 3: Combined formula to check Master sheet and fetch Supplier name from Sheet2 if missing in Master

If you want to check if a UTR from Master (Sheet1) is missing in Master but present in Sheet2 and fetch Supplier Name:

Note:

• Replace A2 with the cell reference for the UTR you are checking.

• If you want to compare data across different workbooks, make sure both workbooks are open or use full paths.

If you have Excel 365 or Excel 2021, you can use the simpler XLOOKUP formula which can look left or right: