# End Result of Formula

Occasional Contributor

# End Result of Formula

Good day,

I do not know how how to use this forum and I don't know how to properly use formulas. But here goes.

I have information in columns E And O & I that can be different depending on the line number.  I want my formula to check if the info in column e in a specific line number matches that of anything in column o.  If it does then it needs to multiply the info in column I of the same line number with that of the info that is in column N same line number as column O.  My formula looks like this :

=+I3*N9+IFS(E3=\$O\$7;I3*\$N\$7;E3=\$O\$8;I3*\$N\$8;E3=\$O\$9;I3*\$N\$9;E3=\$O\$10;I3*\$N\$10;E3=\$O\$11;I3*\$N\$11;E3=\$O\$12;I3*\$N\$12;E3=\$O\$13;I3*\$N\$13;E3=\$O\$14;I3*\$N\$14;E3=\$O\$15;I3*\$N\$15;E3=\$O\$16;I3*\$N\$16;E3=\$O\$17;I3*\$N\$17;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;I3=N26;0)

In this specific instance, it should give me the answer of column n10 multiplied by column I 3 and the answer must be in column k line 3  If I do a simple calculation test then my answers do not match.  Can you help me fix this, please?

https://1drv.ms/x/s!At19_BkxwIeUlO9oKX_BMD79fhEcjQ?e=AlRMvE

Thank you everybody I will definitely use this help.  Love learning to use these formulas

16 Replies

# Re: End Result of Formula

@Wilma_Lombaard_SAWR This is near to impossible to decipher without the file on hand. Can you share it via Onedrive, Google docs or similar? Or at least a screenshot showing all the cells referenced in this formula. And I guess that the string of ;;;;;;;;;;;;; is a typo.

# Re: End Result of Formula

``=IFERROR(INDEX(\$I\$3:\$I\$10,MATCH(E3,\$O\$3:\$O\$10,0))*INDEX(\$N\$3:\$N\$10,MATCH(E3,\$O\$3:\$O\$10,0)),"")``

Is this what you are looking for?

# Re: End Result of Formula

Thaank you, I have no idea if that is what I am looking . I am going to share a test file now

# Re: End Result of Formula

Date Order Placed Chain Store Store No. : Principal Order No. Order Value Incl. Invoiced Number Amount Agent Commission Due Check if TRUE

25 Feb 22 Shop 1 Faketown 1 7010 Supplier 4 123 R 20 374,91 Not on Statement R 10,00 TEST R0,40 R0,10
25 Feb 22 Shop 1 Faketown 2 7011 Supplier 7 456 R 14 637,58 Not on Statement R 10,00 TEST R0,35 R0,15
29 Mar 22 Shop 2 Faketown 3 7012 Supplier 10 789 R 2 980,10 Not on Statement R 10,00 TEST R0,40 DAN R0,25
29 Mar 22 Shop 2 Faketown 3 7012 Supplier 11 101112 R 2 980,10 Not on Statement R 0,00 TEST R0,00
N7 1,00% Supplier 1 1
N8 2,50% Supplier 2 2
N9 3,00% Supplier 3 3
N10 1,00% Supplier 4 4
N11 1,50% Supplier 5 5
N12 3,00% Supplier 6 6
N13 2,50% Supplier 7 7
N14 3,50% Supplier 8 8
N15 3,50% Supplier 9 9
N16 2,50% Supplier 10 10
N17 2,00% Supplier 11 11
N18 3,0% Supplier 12 12
N19 5% Supplier 13 13
N20 2% Supplier 14 14
N21 1,5% Supplier 15 15
N22 3% Supplier 16 16
N23 3% Supplier 17 17
N24 2% Supplier 18 18
N25 3% Supplier 19 19
N26 0% Zero 20

# Re: End Result of Formula

Oh my that doesn't look good. How do I share the actual test document?

# Re: End Result of Formula

@Wilma_Lombaard_SAWR Share it via OneDrive or similar if you can.

# Re: End Result of Formula

Open full text editor.

Then you see this:

# Re: End Result of Formula

Thank you I will try and remember that

Riny_van_Eekelen

# Re: End Result of Formula

``=XLOOKUP(E3,\$O\$7:\$O\$26,\$N\$7:\$N\$26)*I3``

And remember best practices:

Use tables.

No blank rows or blank in tables.

No direct references (as in E3). Use data validation or type the value.

# Re: End Result of Formula

Or, if XLOOKUP is not available

``=IFNA( \$I3*INDEX(\$N\$7:\$N\$26, MATCH(\$E3, \$O\$7:\$O\$26, 0 ) ), 0 )``

# Re: End Result of Formula

``=INDEX(\$N\$7:\$N\$26,MATCH(E3,\$O\$7:\$O\$26,0))*I3``

You can try INDEX and MATCH.

# Re: End Result of Formula

So this is a commission calculation document.
If the commission is coming from Supplier 4 for this order. I will enter the supplier name in column e.
There are however 19 suppliers so before I can calculate the commission in column K, I need to establish that my formula chooses the correct % commission to calculate the commission from the amount. Does that make some sense?

# Re: End Result of Formula

@Wilma_Lombaard_SAWR I believe either of the other respondents have already given the solution to your question.