=IF cell lookup

[pmidwest]

I am making a workbook to calculate the prices of materials we will need for operations.

There are 3 different machines that a user can select from. Each of these machines needs anywhere from 2 to 4 adapters. how can I make the adapters cell look up what machine the user selected in A2 and say ok this machine needs 2 adapters or this machine needs 4 and then look at the quantity the user selected for the machine and say they selected 2 machines that need 2 adapters make the total in the quantity of adapters 4... I hope this makes since to someone because I'm lost...

Thanks

Paul

 

[SkipVought]

Hi,
This is a classic database case. You have a need for a Table of Machine Adapters...

Machine    Adapter
Mach1      Adapt1
Mach1      Adapt2
Mach2      Adapt3
Mach2      Adapt4
Mach2      Adapt5
....

or maybe you just need the Adapter Quantity

Machine    AdapterQty
Mach1      2
Mach2      3
....

You name the ranges with the names in the heading via Insert/Name/Create/Create names in TOP ROW

Then the LOOKUP to return the AdapterQty is...

=INDEX(AdapterQty,MATCH(MachName,Machine,0),1)

where MachName is a reference to the Machine Name data on your sheet.

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[pmidwest]

ok
MachineName AdaptQty
Mach1 4
Mach2 4
Mach3 1

now Ive done everything you said and it works great to an extent.

ITEM Quantity
Mach1 2
Adapt 4
This is what I get... I need it to say you've got 2 machines there and each one needs 4 adapters and change that adapt from 4 to 8

 

[SkipVought]

Well, obviously, you have to use the product of your qty and the lookup qty, or am I missing something? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[pmidwest]

Nevermind I just added *E9 (quantity) to the end of the formula you gave me and it works now... Thanks alot