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.
 

Some videos you may like

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
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
12,593
Office Version
  1. 2007
Platform
  1. 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
12,593
Office Version
  1. 2007
Platform
  1. Windows
Re: Help with a lookup formula

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

Watch MrExcel Video

Forum statistics

Threads
1,108,992
Messages
5,526,109
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top