Forum Discussion
Return value next to cell with multiple source arrays
Hi!
This seems like a simple problem but I cant figure it out.
So I have two tables with different values. I want to inspect the lower rows of the both tables (highlighted with grey) and find a certain value (in this picture 54). When I find the value, I want to know what is the value above it (44 in this case).
In the picture it seems to be working just fine, but if I change the "54" to something on the upper grey row (for ex. 29) it doesn´t return the "19" I would like it to.
And I know the problem but don´t know how to fix it!
As you can see in the picture above, the formula searcher the value (54) from only the lower part. So what I need, is kind of a way to tell the formula that "I want to to look for the value from E4:N4 AND E10:N10, not just one of them.
I know this probably isn´t the best way to explain this but I hope you understand, thanks!
3 Replies
As variant
=SUM( ($E$4:$N$4=C2)*$E$3:$N$3 + ($E$10:$N$10=C2)*$E$9:$N$9 )assuming all numbers are different
=IFERROR(IF(NOT(ISNA(INDEX(E9:N9,(MATCH(C2,E10:N10,0))))),INDEX(E9:N9,(MATCH(C2,E10:N10))),INDEX(E3:N3,(MATCH(C2,E4:N4,0)))),"not found")
Is this what you are looking for?
