Need Two-Way Lookup Using 2 Lookup Criteria and Two Worksheets

**CM_Marsh** · 11-21-2012, 01:05 PM

Hello Forum Users: I've been fussing with a two-way lookup for some time and finallly decided to ask for help here. I am using two criteria that must match on 1 sheet to pick up a value on a second sheet. The same 2 criteria on the second sheeet must match the 2 criteria on the first sheet. Vlookup isn't the answer at least not totally so I've been working with INDEX and MATCH with no luck. I've attached the Excel file for more info. Any assistance would be greatly appreciated. I've been rusty in Excel for some time.

Thank you.

Celeste M

**Søren Larsen** · 11-21-2012, 01:15 PM

Try this for C2 and copy down:

Formula:

Please Login or Register  to view this content.

'tis what you're looking for?

BTW: This does the same:

Formula:

Please Login or Register  to view this content.

It is required for both formulas, that the values on your first sheet are uniquely identified by columns A and B. Otherwise it will take the sum of the values in C for all duplicate rows in terms of their values in A and B.

Using the following "look-up" approach eliminates the problem mentioned above:

Formula:

Please Login or Register  to view this content.

...but be aware that it is an array-formula, and thus it MUST be confirmed using Ctrl+Shift+Enter, and not just Enter.

**CM_Marsh** · 11-21-2012, 02:28 PM

Thanks so much, Soren! The second formula appears to be working now. I will try it on my real data set and see if it still works. If so, I'll post this as "Solved". The "If" inside the formula is what I've been missing. I don't want to sort any of the data before I populate the RL column.

PS - the first formula only worked for a few cells...

Celeste Marsh

**CM_Marsh** · 11-21-2012, 02:47 PM

Thanks Soren - it looks like it's going to work. I may have to tweak some of the ranges but super!

Celeste Marsh

**Søren Larsen** · 11-22-2012, 05:09 AM

You're very welcome!

With regards to the first formula I provided, the reason why it didn't work was that had forgotten a few "$". I've edited the first formula in post #2; so it should work now as well.