MS Excel, lookup multi value.

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

Dear teylyn,

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

There is only 1 way is lookup.

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

Ha,

Thank you!

It is perfect solution.