Thanks:  0
Likes:  0

# Thread: Multiple matches using the IF function

1. 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!

2. 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?

3. could it be

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

??

4. 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

5. Hi Jrrt,

Hi JRRT,

=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

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

7. 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 ]

8. 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

9. 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)

10. 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?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•