Variable materials and prices formula

Hi Everyone

I have attached a sample workbook to try and explain my challenge.
In cell F:2 I need a formula that uses the values in cell A:2 and Cell C:2.
These values change according to what the user inserts. The formula needs to cover a wide spectrum of variables.
eg: IF cell A:2(PRODUCT) = "WOOD"
AND cell C:2(PRODUCT) = 3
THEN = cell B:2(PRICES) ie: R2.00


OR

IF cell A:2(PRODUCT) = "WOOD"
AND cell C:2(PRODUCT) = 6
THEN = cell C:2(PRICES) ie R4.00

Etc Etc

Once I have a formula worked out I need to copy it down in column F.

I have inserted a formula in F:2 , but this only has one variable .
So basically the formula could be something like this I think.


=IF(AND(A:2="WOOD",C:2=3),PRICES!B:2,IF(AND((A:2="WOOD",C:2=6),PRICES!C:2etc etc etc

Maybe I totally of track here and there is an easier way.

Pleases help

Onesock

Originally Posted by BB1972

Hello Onesock,

Please Login or Register  to view this content.

in F2 should do the trick for you.

(Please note I copied your price info to the sheet I was working on for convenience - you can just change the cell references to the PRICES sheet if you're happy with it.)

compact version

=INDEX(L1:Q6,MATCH(A2,K1:K6,)+1,MATCH(C2,INDEX(L1:Q6,MATCH(A2,K1:K6,),),))

Hi BB and TM

First off thank you for your generosity in sharing info .The formulas really work a treat. You people are really a great help.
OK - so now a bit more !! . Can the formula be written so the #N/A is NOT shown when a cell is not being used,but instead the cell is left blank. Or though , if a specific thickness doesnt correspond with the material in the table the cell then could read #N/A or something else to show that ,that material doesnt have that specific thickness .
For example if I inserted 8 into the "WOOD" thickness it would return #N/A or some form of warning.

See revised sheet for example

Bump no response

Bump no response

Hi Ace_XL

Thats fantastic !! Only one more issue.
If I insert a thickness that does not correspond with a thickness of that particular material in the table, is there a way that some form of error return comes up in cells F: and G:.

Thanks

Onesock

Hi BB

Again - good call , or though it leaves "CHECK SIZE" in all the F column cells. This is because I have copied it down a few rows.
In my actual spreadsheet template I have 50 or so rows that need to be filled in. If the cells in column F say "CHECK SIZE" because no other info is given , it will be untidy.
I hope you would be able to get to the bottom of this and figure out a way to only show "CHECK SIZE" if a thickness is NOT corresponding to the material thickness in the table.
I have attached the spreadsheet again for you to see.

Thanks again

Onesock

Hi Again

Sorry Ace_XL that was also meant for you.

Onesock