Multiple drop down menus to give visit frequency

hojsholm

New Member
Joined
Jun 28, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi All
Facing a issue I would like to solve. I have to drop down menus

First drop down is list of suppliers type (A,B,C,D etc)

Second is criticality (Critical, major and Minor)

I have created a Matrix which combining supplier type with criticality and this gives number of visits per year.

I would now like to use this matrix on all our 900 suppliers
My question is how can I set-up a formula to give me visit frequency ( based on the two drop down menus of supplier type and criticality
 

Attachments

  • Excel.PNG
    Excel.PNG
    8.9 KB · Views: 3

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,679
Welcome to the MrExcel forum!

If your table is in A1:D10, and your dropdowns are in F2 and G2, then try:

Book1
ABCDEFGH
1Supplier TypeCriticalMediumLowSupplier TypeLevelVisits
2A2N/AN/ACMedium6
3B3N/A1
4C462
5D573
6E8N/A
7FN/A94
8G519
9HN/AN/AN/A
10
Sheet4
Cell Formulas
RangeFormula
H2H2=VLOOKUP(F2,A2:D10,MATCH(G2,B1:D1,0)+1,0)
 

hojsholm

New Member
Joined
Jun 28, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel forum!

If your table is in A1:D10, and your dropdowns are in F2 and G2, then try:

Book1
ABCDEFGH
1Supplier TypeCriticalMediumLowSupplier TypeLevelVisits
2A2N/AN/ACMedium6
3B3N/A1
4C462
5D573
6E8N/A
7FN/A94
8G519
9HN/AN/AN/A
10
Sheet4
Cell Formulas
RangeFormula
H2H2=VLOOKUP(F2,A2:D10,MATCH(G2,B1:D1,0)+1,0)
Dear Eric
Thanks a lot for you swift reply
I have tried but encounter a problem when I select the Low criticality the value is for column B and not C, Any suggestions??

Best regards
Thomas Schmidt
 

Attachments

  • Excel_Cal.PNG
    Excel_Cal.PNG
    43.2 KB · Views: 2

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,679
Looks like you left off a parameter:

=VLOOKUP(F2;A2:D19;MATCH(G2;B1:D1;0)+1;0)
 

Forum statistics

Threads
1,148,020
Messages
5,744,361
Members
423,864
Latest member
GolfingTitan116

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top