Link to home
Start Free TrialLog in
Avatar of Wonderwall99

asked on

MS Excel return value based on multiple criteria

MS Excel.  I need a formula that returns the value in Column J (workbook #2) if the values in Column C2 & E2 (workbook#1) matches the same combination of values in Column A & C (workbook#2).  I'll be using the solution on a larger set of data so the end result will be greater than just returning values for the combo of C2 & E2 (workbook#1)

Please do not use VBA for a solution, I'd like something more simple to edit later if needed.

Avatar of byundt
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you find the recalc time annoying when you copy the suggested formula down, please consider building an auxiliary column that concatenates the values in Workbook 2 columns A and C. Assuming this concatenation is in column Z, you may then use an INDEX & MATCH formula like:
=IFNA(INDEX('[Workbook 2.xlsx]Sheet1'!J$2:J$10000, MATCH(C2 & E2,'[Workbook 2.xlsx]Sheet1'!Z$2:Z$10000,0)), "Not found")

Open in new window