Forum Discussion
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
- https://1drv.ms/x/s!At19_BkxwIeUlO9oKX_BMD79fhEcjQ?e=WeDn46
- Riny_van_Eekelen
Or, if XLOOKUP is not available
=IFNA( $I3*INDEX($N$7:$N$26, MATCH($E3, $O$7:$O$26, 0 ) ), 0 )
- 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- Oh my that doesn't look good. How do I share the actual test document?
=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?
- Thaank you, I have no idea if that is what I am looking . I am going to share a test file now
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.
