Forum Discussion

amjad1987's avatar
amjad1987
Copper Contributor
May 15, 2023
Solved

INDEX MATCH picking up wrong/duplicate value when using with excel table? MS Office 2021

Hi, Hope everyone is fine there. I am using Microsoft Office 2021 LTSC v2108 Build 14332.20481. I have only two sheets only everything is working fine but sometimes INDEX MATCH picking up wrong/dupl...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 16, 2023

    amjad1987 I'd say that the formulas in O, P and Q are wrong and that they produce the correct result sometimes, only by chance.

     

    Wrong formula:

    =IFERROR(INDEX(Main[Style No.],MATCH($N18,Main[PO/SO No.]),0),"")

     

    Correct formula:

    =IFERROR(INDEX(Main[Style No.],MATCH($N18,Main[PO/SO No.],0)),"")

     

    Thus, move the right parenthesis as indicated and it should work as desired.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 16, 2023

amjad1987 I'd say that the formulas in O, P and Q are wrong and that they produce the correct result sometimes, only by chance.

 

Wrong formula:

=IFERROR(INDEX(Main[Style No.],MATCH($N18,Main[PO/SO No.]),0),"")

 

Correct formula:

=IFERROR(INDEX(Main[Style No.],MATCH($N18,Main[PO/SO No.],0)),"")

 

Thus, move the right parenthesis as indicated and it should work as desired.

  • amjad1987's avatar
    amjad1987
    Copper Contributor
    May 21, 2023

    Riny_van_Eekelen thanks a lot for your reply. Yes you're right dear it was a little problem of that ) which i just move the right as you indicated and it worked. Actually because it was working when i just started to enter my data so i couldn't notice it.

    Problem Solved

    Thanks

Resources