Lookup formula

buddydubbo

New Member
Joined
Nov 1, 2011
Messages
7
jobtaskchargecodesprices
N34cable mod plate125cable40
N35cable socket modconnect10
N38socket connect modplate15
N44cable plate socketsocket25
mod70

<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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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
Joined
Nov 1, 2011
Messages
7
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
Joined
Dec 3, 2018
Messages
9,789
Office Version
2007
Platform
Windows
Re: Help with a lookup formula

Try this

Excel Workbook
ABCDEF
1jobtaskchargecodesprices
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,789
Office Version
2007
Platform
Windows
Re: Help with a lookup formula

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

Forum statistics

Threads
1,086,259
Messages
5,388,729
Members
402,137
Latest member
pkulkarni

Some videos you may like

This Week's Hot Topics

Top