Making a match on a specific byte count

[Jaimee001]

Greetings all!

I have a report,
Column A has 135 Rows
Column b has 746 Rows
Column A1 = ADTSEC5226.J87364
The match in column B is in row 14
The match has to be made on the first 5 bytes of the cell

I had a formula that makes a match straight across
original formula: IF(RIGHT(A2,5)=RIGHT(B2,5),"Yes","No")

I took that formula and changed it to this:
tweaked formula: IF(RIGHT(A2,5)=RIGHT($B$2:$B$746),5),"Yes","No")

However it doesn't work.
Is it possible to make a match using an array?


Regards,
Jaimee

 

[Nebu]

Hi:

Use the following formula. I have given column A as range and column B as look up, I guess it will work for you anyways. This is an array formula execute it by pressing control+shift+enter keys.

=IF(ISNUMBER(MATCH(RIGHT(B1,5),RIGHT($A$1:$A$6,5),0)),"Yes","No")

Note: since you did not have a sample file. I am giving the formula based on my understanding.

Thanks

 

[Jaimee001]

Hi:

Use the following formula. I have given column A as range and column B as look up, I guess it will work for you anyways. This is an array formula execute it by pressing control+shift+enter keys.

=IF(ISNUMBER(MATCH(RIGHT(B1,5),RIGHT($A$1:$A$6,5),0)),"Yes","No")

Note: since you did not have a sample file. I am giving the formula based on my understanding.

Thanks

Thank you Nebu!!! It worked!!!!