MS Excel, lookup multi value.

I have an excel file (attached file).

I want to make a formular to lookup base on 2 parameters.

How can I do that?

Please check attached file for detail.

Thank you!
leadtime-file--1.xlsx
JameMeckAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
For some reason, the Countifs formula returns some weird results. But this Sumproduct seems to do the trick nicely, starting in cell O5 and copied across and down.

=SUMPRODUCT(--($A$1:$A$250=$M5),--($B$1:$B$250=O$4))
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

why don't you just build a pivot table with company name in the rows, lead time in the columns and perform a count on the records?

cheers, teylyn
0
 
JameMeckAuthor Commented:
Dear teylyn,

The original data table I cannot edit, the column count I inserted and manual input.

There is only 1 way is lookup.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Problem is that a pivot table is not a good source for further analysis. The labels appear only in one cell.

In Excel 2010 you can change that, though. Click the pivot table, then in the Design ribbon click Report Layout > Show in Tabular form. Then click Report Layout > Repeat all item labels.

Now you can use a Countifs function in your other table. Starting in cell O5

=COUNTIFS($A:$A,$M5,$B:$B,O$4)

Copy across and down. Format with custom format

0;;

to suppress zeros, if desired. Column K is no longer required.

Mind you that the table labels in row 4 must be EXACTLY the same text as in the pivot table. I had to correct many of them.

See attached.
leadtime-file--1--1-.xlsx
0
 
JameMeckAuthor Commented:
Ha,

Thank you!

It is perfect solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.