End Result of Formula

[Wilma_Lombaard_SAWR]

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

[#2]

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

[#3]

@Wilma_Lombaard_SAWR 

=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? 

[#4]

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

[#5]

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

[#6]

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

[#7]

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

[#8]

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

[#9]

@Wilma_Lombaard_SAWR 

Open full text editor.

Then you see this:

[#10]

Thank you I will try and remember that

[#11]

Riny_van_Eekelen

[#12]

@Wilma_Lombaard_SAWR 

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

[#13]

@Wilma_Lombaard_SAWR Have downloaded your file but don't really understand what you want to achieve.

[#14]

@Wilma_Lombaard_SAWR 

Or, if XLOOKUP is not available

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

[#15]

@Wilma_Lombaard_SAWR 

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

You can try INDEX and MATCH.

[#16]

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?

[#17]

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