I'm thinking how I would tackle this and that would be with a custom function
(a macro). If you need to keep it to standard Excel functions then you may
want to post in the microsoft.public.excel.functions group. It sounds like a
complex formula because the number of entities can vary.
--

Toby Erkson
http://excel.icbm.org/


"markythesk8erboi" wrote:

These tips were both VERY helpfull and thank you both. However, I'm still
running into a couple of problems with it. I need to be able to input all
data into the one cell and get the outcome in another. In other words, I need
the VLOOKUP to be able to see 1S, 1L as two different entities and give me
the sum of each. That poses yet another problem. When it comes to the
quantities of each of the two(or however many) entities. Maybe this wasn't
meant to be. LOL But thank you again for your assistance. :-)

"Air_Cooled_Nut" wrote:

Your lookup cell will contain the formula:
=VLOOKUP(A2,MyTable,2,FALSE)
where A2 is the cell you're checking (or change it to whatever cell you want
to) and MyTable is the name of the range of cells that contain the lookup
table. Here's what your lookup table should look like:
1S 0.24
1L 0.41
2S 0.43
3PL 0.51
SPL 0.78
TPL 2.37
NTPL 1.36
TTPL 3.5
You put your lookup values in one column and their corrisponding result in
the column next to it.

This is the simple way. Doing the math would be:
=2 * VLOOKUP(A2,MyTable,2,FALSE)
or
=A3*VLOOKUP(A2,MyTable,2,FALSE)
where cell A3 contains the number you want to use as the multiplier.

Toby Erkson
http://excel.icbm.org/

"markythesk8erboi" wrote:

I currently have this formula in place :

H9=IF(G9="1S",0.24,IF(G9="1L",0.41,IF(G9="2S",0.43 ,IF(G9="3PL",0.51,IF(G9="SPL",0.78,IF(G9="TPL",2.3 7,IF(G9="NTPL",1.36,IF(G9="TTPL",3.5,0))))))))

1) Is there an easier way to have this outcome?
2) How do I set up the formula so that I can input multiple phrases? i.e.
"1L,2S,SPL" = 0.41+0.43+0.78.
3) Can I also set up quantities of each? i.e. "2,1L,1,2S,1,SPL" = (2 x 0.41)
+ (1 x 0.43) + (1 x 0.78)

Please keep in mind you will have to be very clear to me what I must do for
I am dumb. LOL
Thank you for your help in this matter!

Mark Feldman