Lookup formula

[buddydubbo]

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]job[/TD]
[TD="align: center"]task[/TD]
[TD="align: center"]charge[/TD]
[TD][/TD]
[TD="align: center"]codes[/TD]
[TD="align: center"]prices[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N34[/TD]
[TD]cable mod plate[/TD]
[TD="align: center"]125[/TD]
[TD][/TD]
[TD]cable[/TD]
[TD]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N35[/TD]
[TD]cable socket mod[/TD]
[TD][/TD]
[TD][/TD]
[TD]connect[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N38[/TD]
[TD]socket connect mod[/TD]
[TD][/TD]
[TD][/TD]
[TD]plate[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N44[/TD]
[TD]cable plate socket[/TD]
[TD][/TD]
[TD][/TD]
[TD]socket[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]mod[/TD]
[TD]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.