Forum Discussion
Fomula issue
Hi All,
Can someone take a look at my formula and help me understand why the cells in column L is not taking the information correctly? Could it be a formatting problem? Thank you in advance.
11 Replies
Bahama85 Try this:
=IFERROR(INDEX(Q:Q,MATCH(1,($B:$B=N6)*($A:$A=O6)*($K:$K=P6)),1),"")I believe you were missing "(" directly before $B:$B and also an extra ")" directly after P6 to close out the match function, lastly, the zero should be a 1 to indicate the column number of the original array.
- I believe that was it. Now I get an error message that states "Inconsistent calculated column formula"
Bahama85 That just means that the same calculation is not in all rows within the table. If you copy and paste in all rows that should disappear.
If I follow correctly, I think you can simplify this. That INDEX-MATCH arrangement uses a lot of calculation resources.
Adjust ranges as needed.
Non-dynamic array solution (Ctrl+Shift+Enter):
=VLOOKUP(A6&B6&K6,CHOOSE({1,2},$O$1:$O$10&$N$1:$N$10&$P$1:$P$10,$Q$1:$Q$10),2,0)365 Solution:
=XLOOKUP(A6&B6&K6,$O$1:$O$10&$N$1:$N$10&$P$1:$P$10,$Q$1:$Q$10)
