Multiple matches using the IF function

JRRT · Mar 21st, 2002, 12:18 PM

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!

Aladin Akyurek · Mar 21st, 2002, 12:24 PM

Quote:

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?

IML · Mar 21st, 2002, 12:29 PM

could it be

=VLOOKUP(E2,$A$24:$D$28,4,0)*D2

??

JRRT · Mar 21st, 2002, 12:40 PM

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

Jay Petrulis · Mar 21st, 2002, 12:48 PM

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

JRRT · Mar 21st, 2002, 01:03 PM

The formula =VLOOKUP(E2,$A$24:$D$28,4,0)*D2 seems to work. What is the "4,0" towards the end used for?

Mark W. · Mar 21st, 2002, 01:10 PM

Quote:

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 ]

JRRT · Mar 21st, 2002, 02:36 PM

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

Aladin Akyurek · Mar 21st, 2002, 03:14 PM

Quote:

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

JRRT · Mar 21st, 2002, 03:33 PM

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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 12:18 PM	#1
JRRT New Member Join Date: Mar 2002 Posts: 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)D2D24. Thanks for your help this site is amazing!

Mar 21st, 2002, 12:29 PM	#3
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	could it be =VLOOKUP(E2,$A$24:$D$28,4,0)*D2 ??

Mar 21st, 2002, 12:40 PM	#4
JRRT New Member Join Date: Mar 2002 Posts: 21	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

Mar 21st, 2002, 12:48 PM	#5
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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

Mar 21st, 2002, 01:03 PM	#6
JRRT New Member Join Date: Mar 2002 Posts: 21	The formula =VLOOKUP(E2,$A$24:$D$28,4,0)*D2 seems to work. What is the "4,0" towards the end used for?

Mar 21st, 2002, 02:36 PM	#8
JRRT New Member Join Date: Mar 2002 Posts: 21	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

Mar 21st, 2002, 03:33 PM	#10
JRRT New Member Join Date: Mar 2002 Posts: 21	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?