• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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
0
JameMeck
Asked:
JameMeck
  • 3
  • 2
1 Solution
 
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
 
JameMeckAuthor Commented:
Ha,

Thank you!

It is perfect solution.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now