# Creating Formula - Is it even possible?

#### tnemom

##### Board Regular
test.xls
HIJKLMNOPQRSTUVWXYZ
1CatID#ELECTMTCHoursInv.Material\$Mutiplier09645096550965609751097520975709889102361067310675106761084210877
2108429655#N/A1.5813310317206365573
396459655#N/A1402310317206365573
Buchanan Survey

This is a partial of the sheet that I am working on.

The current Formula in Column L is manually entered for each line. What I would like to know is if there is a way to have a macro that would create this formula?

What we would need it to do is check out the Cat ID #'s in cells H - J and based upon the numbers in there, look up the \$ amount (right now they are in columns N-Z). The amount in column M would be manually input and then the formula in column L would be

the value in M multiplied by the sum of the values that correspond with the ID #'s in H-J plus 10.

Is this possible?
Thanks,
TNEMOM

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Is there any reason why the values in columns N - Z must be repeated in each row? If not, these could be placed on another sheet in your workbook and then it becomes a simple matter of using either HLOOKUP to capture the Cat ID values you need for your formula.

No they don't need to be repeated - I plan on making a list on an alternate sheet. This is the way the sheet was presented to me and I was asked if I could simplify since the user manually enters everything.

If I could get assistance with the formula, it would be much appreciated.

Tnemom

Is there anyone who could help me with the formula?

Thanks

Try this formula in L2 copied down

=(SUMPRODUCT(--ISNUMBER(MATCH(N\$1:Z\$1,H2:J2,0)),N2:Z2)+10)*M2

Hi

How do you come up with the value in L3? Shouldn't it be looking up 9645 and 9655, giving a result of 46?

Yes, that should be the result in L3...the formula is wrong.

Barry,

When I put that formula in L2 - I get the result of 51 not 81

OK, I suspect that I2 is not deemed to be a match with O1 because I2 is a numeric value 9655 and O1 is a text formatted 09655.

You probably need to change N1:Z1 so these are all numeric values

You can do that like this:

Copy any empty cell
Select N1:Z1
Edit > Paste Special > "add" > OK

If you wish you can display 9655 as 09655 by using a custom format of 00000 in N1:Z1

Thanks Barry!!

Works perfectly now

Replies
1
Views
272
Replies
4
Views
82
Replies
0
Views
84
Replies
4
Views
98
Replies
1
Views
151

1,211,882
Messages
6,104,557
Members
447,915
Latest member
jpearson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back