lookup for a value based on multiple criteria

HI, welcome to the forum

Probably the easiest/simplest way to do this would be to create a helper column in each sheet and combine all the relevant columns - then use that in a VLOOKUP() (IF it is the left-most column) or INDEX/MATCH to bring back the value you want

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Hi HolyExcel,
Here are the steps:-
In J2 put the Invoice Number ie. INV12345

In K2, Put the

Formula:

Please Login or Register  to view this content.

Then in M2, put

Formula:

Please Login or Register  to view this content.

as an Array formula, Use CTRL + SHIFT + ENTER to confirm it NOT JUST Enter.

Drag the formula horizontally till S2 and then Downwards Accordingly to show all matches.
Check the attached file

lookup for a value based on multiple criteria.xlsx

I2=IFERROR(INDEX('Version 1'!$H:$H,SMALL(INDEX(((('Version 1'!$A$2:$A$12<>$A2)+('Version 1'!$B$2:$B$12<>$B2)+('Version 1'!$C$2:$C$12<>$C2)+('Version 1'!$D$2:$D$12<>$D2)+('Version 1'!$E$2:$E$12<>$E2)+('Version 1'!$F$2:$F$12<>$F2))<>0)*10^10+ROW('Version 1'!$A$2:$A$12),0),COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2,D$2:D2,D2,E$2:E2,E2,F$2:F2,F2,G$2:G2,G2))),"")
Try this

can try this too
I2=IFERROR(INDEX('Version 1'!$H:$H,SMALL(INDEX((MMULT(('Version 1'!$A$2:$G$12<>$A2:$G2)*1,{1;1;1;1;1;1;1})<>0)*10^10+ROW('Version 1'!$A$2:$G$12),0),SUMPRODUCT((MMULT(($A$2:$G2<>A2:G2)*1,{1;1;1;1;1;1;1})=0)*1))),"")
try this and copy towards down

Sorry I misinterpreted it earlier..
Now Try this..

Formula:

Please Login or Register  to view this content.

Check the attached file:-
lookup for a value based on multiple criteria (1).xlsx