Forum Discussion
Index MATCH formula returns the next value in an array
Hello Would appreciate if I can get some help on this problem. If you please look at the attached excel file, I am trying to lookup value from one data set, into another. However, the index match f...
You may try the following Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
In C3
=IFERROR(INDEX($G$3:$G$5,SMALL(IF($E$3:$E$5=A3,IF($F$3:$F$5=B3,ROW($E$3:$E$5)-ROW($E$3)+1)),ROWS(C$3:C3))),"Record not in File B")Confirm it with Ctrl+Shift+Enter and then copy it down.
You're welcome! Glad it worked as desired.
Please take a minute to accept the solution posted as an Answer, that would mark your question as Solved.
apologies I might have jumped the gun there!
I have a follow-up question please. If you looked at the excel file, I changed name of person from Jamie to josh and I excepted the formula to pick the value '12' in the third row. But that's not what the formula is doing - any idea why this is happening?
Thank you
Never mind.
Please try the following one...
In C3
=IFERROR(INDEX($G$3:$G$5,SMALL(IF($E$3:$E$5=A3,IF($F$3:$F$5=B3,ROW($E$3:$E$5)-ROW($E$3)+1)),COUNTIFS($E$3:E3,A3,$F$3:F3,B3))),"Record not in File B")