Forum Discussion

SteveOC's avatar
SteveOC
Copper Contributor
Feb 05, 2020

Index Match combined with Right where Data in cell contains Alpha not numeric

Hello. I have been playing with Index Match to lookup values in multiple spreadsheets - either within a workbook or across workbooks and generally don't have an issue. The situation I have now is w...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 05, 2020

SteveOC 

Steve, with MATCH(RIGHT($A:$A,4)*1,RIGHT(Snipe!$C:$C,4),0) you lookup values for entire column A:A within column Snipe!$C:$C,. If you are on Excel with dynamic arrays, formula returns an array of the entire column size. If not, it returns first element of such array. #N/A also will be, but somewhere in the middle of the array.

 

I guess you need to modify the formula on MATCH(RIGHT($A1,4)*1,RIGHT(Snipe!$C:$C,4),0) and drag it down.

SteveOC's avatar
SteveOC
Copper Contributor
Feb 05, 2020

SergeiBaklan 

 

Hello Sergei - thanks for the rapid response.

 

I tried your suggested change but it seems to return the same results. I don't want to post the spreadsheet as it contains real people but perhaps I could construct a mockup using dummy data and try to post it.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 05, 2020

    SteveOC 

    Yes, with some sample file it'll be much easier to find the solution

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Feb 06, 2020

        SteveOC 

        Steve, you have mix of texts and numbers in lookup range (like V0116 and 3713).

        I'd take not most right 4 characters, but all characters to the right of the dash and use them in MATCH depends on text or number is returned. Like

        =INDEX(Snipe!$D:$D,MATCH(IFERROR(RIGHT(A1,LEN(A1) - SEARCH("-",A1))*1,RIGHT(A1,LEN(A1) - SEARCH("-",A1))),Snipe!$C:$C,0))

Resources