Forum Discussion
Index Match combined with Right where Data in cell contains Alpha not numeric
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.
Yes, with some sample file it'll be much easier to find the solution
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))Thanks Sergei.
I take the rightmost 4 because of the mix of letters and numbers and not knowing how to deal with that other than stripping off the first character so they are all numeric.
The other problem is that Snipe IT strips leading zeroes when doing an extract to CSV, however I know that for the data I am interested in there is only a zero in position 1.
I will take a look at this and see if it can help me - but I may need to use a helper column in the extract to pad the cells back out to 5 characters adding in the leading zeroes.
Regards
Steve O.
Thanks for the reply.
The Lookup functions seems very like VLOOKUP in that it requires the Lookup column to be sorted in Ascending order. Although in the sample data I provided this is the case simply because I put it together quickly, in my live dataset the data is not sorted in Ascending order.
Although I could presumably sort the data, I started using Index match in preference to VLOOKUP because it did not require me to manipulate the raw data and I would like to know if there is a way to get this to work with Index Match.
Regards
Steve O.
