# Lookup formula

#### buddydubbo

##### New Member
 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 .

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Gerald Higgins

##### Well-known Member
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

##### New Member
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

Last edited:

#### DanteAmor

##### Well-known Member
Re: Help with a lookup formula

Try this

Excel Workbook
ABCDEF
2N34cable mod plate125cable40
3N35cable socket mod135connect10
4N38socket connect mod105plate15
5N44cable plate socket80socket25
6N45mod70mod70
7N46connect10
8N47mod, connet70
9N48plate-connect25
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

##### New Member
Re: Help with a lookup formula

thanks Dante,
this worked a treat.

#### DanteAmor

##### Well-known Member
Re: Help with a lookup formula

Replies
2
Views
250
Replies
6
Views
553
Replies
8
Views
448
Replies
6
Views
336
Replies
6
Views
6K