Lookup formula

[buddydubbo]

job	task	charge		codes	prices
N34	cable mod plate	125		cable	40
N35	cable socket mod			connect	10
N38	socket connect mod			plate	15
N44	cable plate socket			socket	25
				mod	70

<tbody>
</tbody>

hi i new to advance formulas and i need a formula to look at the code words in the task column
and to sum them as a total from the prices next to the codes in the charge column.

so C2 will look at B2 and check if any words from range E2:E6 appear and add the sum of there corresponding price in range F2:F6 .

many thanks in advance.

 

[Gerald Higgins]

Re: Help with a lookup formula

Your request is a little vague.

This would be easier if the entries in column B can be separated into different columns - is that an option ?

Failing that, will there only ever be 3 entries in each cell in column B ?
Will they always be separated by a single space character ?
Will you ever get entries which look like two separate values and contain a single space character but need to be treated as a single value, such as "long cable" ?

 

[buddydubbo]

Re: Help with a lookup formula

i am filling out column B to auto total a price in column C according to the code word and corresponding price

so if i type in B "mod" and "plate" it will auto total 85 in C column

only the code words in the list in column D will be type in column B that can have a space to seperate or a dash or comma what ever works reading like this : mod plate socket or mod,plate,socket

 

[DanteAmor]

Re: Help with a lookup formula

Try this

Excel Workbook
	A	B	C	D	E	F
1	job	task	charge		codes	prices
2	N34	cable mod plate	125		cable	40
3	N35	cable socket mod	135		connect	10
4	N38	socket connect mod	105		plate	15
5	N44	cable plate socket	80		socket	25
6	N45	mod	70		mod	70
7	N46	connect	10
8	N47	mod, connet	70
9	N48	plate-connect	25
Sheet

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[buddydubbo]

Re: Help with a lookup formula

thanks Dante,
this worked a treat.

 

[DanteAmor]

Re: Help with a lookup formula

I'm glad to help you. Thanks for the feedback.