Multiple matches using the IF function

JRRT

New Member
Joined
Mar 19, 2002
Messages
21
The formula I am using will be in F2 I want to have it look at what is in cells A24 through A28 to find the match that is in E2 then multiply it by what is in d2 by what it finds on the match that is the right of the search that it did on A24 through A28 which is in the D24 through D28.
I know this is confusing if you need more of an explination let me know.
I have come up with part of what I need but can't figure how to get the last multiplication part. here is what I have so far: =($A$23:$A$28=E2)*D2*D24. Thanks for your help this site is amazing!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
On 2002-03-21 11:18, JRRT wrote:
The formula I am using will be in F2 I want to have it look at what is in cells A24 through A28 to find the match that is in E2 then multiply it by what is in d2 by what it finds on the match that is the right of the search that it did on A24 through A28 which is in the D24 through D28.
I know this is confusing if you need more of an explination let me know.
I have come up with part of what I need but can't figure how to get the last multiplication part. here is what I have so far: =($A$23:$A$28=E2)*D2*D24. Thanks for your help this site is amazing!

Don't understand at all.

Care to try again using short sentences?
 
Upvote 0
The formula will be in F2 what I would like the formula to do is look at the text that is typed in E2 and match it up with what text is put in cells A24 through A28.
If it finds a match (i.e. A25)it needs to take the Number that is put the same row in cell D24 through D28 (i.e. D25). Then it needs to multiply that number it matched (i.e. the number in D25) by the number that is put in cell d2.
I hope that is better, if not I can e-mail the spreadsheet that would illustrate it better
 
Upvote 0
Hi Jrrt,

Hi JRRT,

How about

=INDEX($1:$65536,MATCH(E2,YourRange,0)+ROW(YourRange)-1,COLUMN(YourRange))*D2

Specifically for you,
=INDEX($1:$65536,MATCH(E2,A24:A28,0)+ROW(A24:A28)-1,4)*D2

Note that there is no error checking here.

HTH,
Jay
 
Upvote 0
The formula =VLOOKUP(E2,$A$24:$D$28,4,0)*D2 seems to work. What is the "4,0" towards the end used for?
 
Upvote 0
On 2002-03-21 12:03, JRRT wrote:
The formula =VLOOKUP(E2,$A$24:$D$28,4,0)*D2 seems to work. What is the "4,0" towards the end used for?

Return the 4th column of the lookup table, $A$24:$D$28. 0 means perform an exact match. See the Excel Help topic for "VLOOKUP worksheet function".
This message was edited by Mark W. on 2002-03-21 12:11
 
Upvote 0
I made a mistake the formula doesn't do the math correctly. I think it finds the right cells but it multiplies them incorrecly. For example it is supposed to multiply D2 which has the number 30 by the number in cell D23 which has the number 79. It gives me the total of 2,401.60 but the correct answer is supposed to be 2,370. I looked at the help in excel but can't figure it out. This is the formula I am using which is located in cell F2: =VLOOKUP(E2,$A$23:$D$28,4,0)*D2
Thanks
 
Upvote 0
On 2002-03-21 13:36, JRRT wrote:
I made a mistake the formula doesn't do the math correctly. I think it finds the right cells but it multiplies them incorrecly. For example it is supposed to multiply D2 which has the number 30 by the number in cell D23 which has the number 79. It gives me the total of 2,401.60 but the correct answer is supposed to be 2,370. I looked at the help in excel but can't figure it out. This is the formula I am using which is located in cell F2: =VLOOKUP(E2,$A$23:$D$28,4,0)*D2
Thanks

Would it be:

=INDEX(D24:D28,MATCH(E2,A24:A28,0))*D2


or better:

=IF(COUNTIF(A24:A28,E2),INDEX(D24:D28,MATCH(E2,A24:A28,0))*D2,0)

Aladin
 
Upvote 0
With both formulas it still comes up with the answer 2401.60 but the answer is supposed to be 2370. I can't seem to find why it is doing it wrong. I tried both formulas and both give the incorrect answer of 2401.60 any ideas why?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top