Jun 05 2022 09:45 AM - edited Jun 05 2022 11:32 AM
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
Jun 05 2022 10:12 AM
@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.
Jun 05 2022 10:13 AM
=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?
Jun 05 2022 10:15 AM
Jun 05 2022 10:21 AM
Jun 05 2022 10:22 AM
Jun 05 2022 10:31 AM
@Wilma_Lombaard_SAWR Share it via OneDrive or similar if you can.
Jun 05 2022 10:32 AM
Jun 05 2022 10:33 AM
Jun 05 2022 10:47 AM
=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.
Jun 05 2022 10:47 AM
@Wilma_Lombaard_SAWR Have downloaded your file but don't really understand what you want to achieve.
Jun 05 2022 10:51 AM
Or, if XLOOKUP is not available
=IFNA( $I3*INDEX($N$7:$N$26, MATCH($E3, $O$7:$O$26, 0 ) ), 0 )
Jun 05 2022 10:53 AM
Jun 05 2022 10:56 AM
Jun 05 2022 11:15 AM
@Wilma_Lombaard_SAWR I believe either of the other respondents have already given the solution to your question.