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

[SteveOC]

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 where I am trying to match a value in for an Asset Number against a substring of a value that represents a name and contains the Asset Number in the rightmost characters.

The Asset numbers come in 2 variations - nnnnn (5 numerics starting at 00000) and Vnnnn (Alpha + 4 numerics starting at 0000)

The first issue is that the software that produces the extract (CSV) from the application drops the leading zeroes from the all numeric entries.

I could perhaps pad them back out, but in fact all of the data that I am interested in has values over 01000 and I have no collisions with Vnnnn values so I am just interested in the last 4 (will always be Digits).

The second issue I had was that the Index Match did not find any results when adding the Right clause until i added a *1 to it.

The third issue - which I am as yet unable to resolve is that it is not returning the result when using the *1 if the cell contains Vnnnn (i.e. it is not all numeric).

Here is the formula that returns the correct results for all instances where Asset is nnnnn (all numerics) but not if it is Vnnnn.

=INDEX(Snipe!$AD:$AD,MATCH(RIGHT($A:$A,4)*1,Snipe!$C:$C,0))

The data looks like this - and I take the Rightmost 4 characters in Column A on one sheet

RESL10-02988

RES64L7-V0216

To match against this in Column C on another sheet  to do the lookup and return the value in another column

Asset Tag

V0216

2988

When the value in C starts with a V - the lookup fails on the cells that are 5 characters starting with a V

which makes sense since with the 0 at the end of the Index Match it isn't an exact match.

Oddly it does not return N/A - it returns a 0.

So I tried adding Right (4) to the values in C - but then I got no results at all - every result was N/A?

 =INDEX(Snipe!$AD:$AD,MATCH(RIGHT($A:$A,4)*1,RIGHT(Snipe!$C:$C,4),0))

Likewise this returns N/A :

=INDEX(Snipe!$AD:$AD,MATCH(RIGHT($A:$A,4)*1,RIGHT(Snipe!$C:$C,4)*1,0))

I tried changing the Columns from General to Text and retrying the formulas to no avail.

Is it possible to get this to work without manipulating the raw data using helper cells?

Regards

Steve O.

[#2]

@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.

[#3]

@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.

[#4]

@SteveOC 

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

[#5]

@SergeiBaklan 

Hello.

Hopefully this will work and show what I am seeing.

Regards

Steve O.

[#6]

@SteveOC 

LOOKUP returns your expected results, as shown in B1 below: 

[#7]

@Twifoo 

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.

[#8]

@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))

[#9]

@SteveOC 

It doesn't matter whether or not the lookup column is sorted. I disarranged the data in the lookup column. Nonetheless, LOOKUP returns the expected results, as shown below: 

[#10]

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.

[#11]

@Twifoo

Thanks again.

I did not try it but instead read a post on Lookup that stated that the Lookup column

needed to be sorted in order (much like VLOOKUP). 

I will take another look.

Regards 

Steve O.