Using Multiple MATCH Tables on 1 spreadsheet

Hello Experts,

Here is my situation ...

I created a MATCH table to assist in creating production standards for a particular piece of machinery in my company. It matches the first 2 criteria nicely and provides me with the correct match information in the following columns. I also use a LOOKUP table to select the specific running speed of the equipment based on the length of the run of the particular job we produce. That also works well.

What I would like to do is to be able to use the same spreadsheet for the 2 different types of machinery we run. Each piece of machinery, although similiar, has different production standards. A simple drop down list to indicate which machine is selected would work fine. When I select "Equipment #1" I would like to use one specific MATCH table and LOOKUP table. When I select "Equipment #2" I would like to use a different MATCH table and LOOKUP table. Is this possible? I'm guessing that I can use an IF THEN statement that says IF cell A1=Equipment #1, then select a specific MATCH and LOOKUP table, and if not then a second MATCH and LOOKUP table would be used. But, I can't fgure out how to set up the IF/THEN for a MATCH and LOOKUP table. It seems easy enough, but I just can't seem to get it right. Any thoughts or help would be appreciated?

Thanks,
Joe

Hello
Take a look at the CHOOSE function it will probably do as you require but it's hard to say for sure without seeing your various formulas and tables.

Hope this helps.
DBY

DBY, Thanks ... I will certainly look at the CHOOSE formula. If I don;t get it accomplished, I will submit the spreadsheet when I get to the PC with the file on it. Thanks again!

DBY,

Although I can't get to the computer with the actual spreadsheet file today, I created a quick version of what I am trying to do. I attached the file. You'll see that I want to select the data from drop down lists in A2, B2 and C2 and I want the results to show up in D2. When I select either of the 2 options in A2, I want it to reference the particular MATCH table for that option which is selected. As it is now, it is only using the table under Equipment #1 even though I may select Equipment #2 in A2. How can a CHOOSE formula work in this case? Any suggestions? Just a quick note ... as this spreadsheet grows, I may add columns to each match table to include other solutions. So, I need any solution to be sure it references the specific table immediately based on my entry in A2. Thank you!

FDibbins,

I see how you used B2&C2 to create the additional column, then a simple vertical look up with the results column as the only different criteria in each formula. I believe I can use this! I'll apply it to the actual spreadsheet on Monday and let you know. But, I believe it will be fine with just a minor change as you showed me. Thanks!!

Joe

Hello
Just by way of an example of the CHOOSE function, if you are using Excel 2003, as an alternative to Fdibbins solution , if you wished to retain your original layout without a helper column:

=CHOOSE(MATCH(A2,$A$12:$A$13,0),SUMPRODUCT(--($D$12:$D$35=B2),--($E$12:$E$35=C2),F12:F35),SUMPRODUCT(--($H$12:$H$35=B2),--($I$12:$I$35=C2),$J$12:$J$35))

This would assume that each combination of Machine; Color and Code was unique, otherwise you would get a multiplication of values.

VLOOKUP, however, is probably more efficient than this alternative.

Regards
DBY

DBY,

Thanks for the example of the CHOOSE function. I didn't know about that but it makes sene now that you explained it. However, I was playing with this again and came up with a pretty somple solution that will work for me. I entered the formula =IF(A2="machine #1", F10,J10) into cell D2 and it works perfectly. I attached the sample. Thanks for everyone's help. I learned several new things which is great!

Joe

@ joebell

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Thanks Cutter, nice to be here! I didn't know about the star icon. I will make sure I go back to the several individuals who taught me something that I will be using. Hopefully I will learn enough to contribute also!