Match/VlookUp using results from a Left/Right combination

[cunningAce]

Hi,

I have in cell B4, 201814, formatted as number.

I am using the following combination of Left & Right to change the number to 201714.

=LEFT(B4,4)-1&RIGHT(B4,2) This gives me the desired result.

I am then using this within a Match or Vlookup formula (both of which return N/A, when the data I am looking up does contain 201714.

Formula example I am using:

=MATCH((LEFT(B4,4)-1&RIGHT(B4,2)),PROJECTIONS!B:B,0)

or

=VLOOKUP((LEFT(B4,4)-1&RIGHT(B4,2)),PROJECTIONS!B:D,3,0)

both returning N/A

Any help much appreciated.

 

[AlanY]

because your left/right turned the number to text, try this to turn it back to number

=(LEFT(B4,4)-1&RIGHT(B4,2))*1

 

[AlanY]

Hi,

I have in cell B4, 201814, formatted as number.

I am using the following combination of Left & Right to change the number to 201714.

=LEFT(B4,4)-1&RIGHT(B4,2) This gives me the desired result.

BTW =LEFT(B4,4)-1&RIGHT(B4,2) effectively is to take away 100 from B4, right?

 

[cunningAce]

As simple as that, Worked perfect thanks.
Noted on your point regarding the minus 100. Much easier

 

[AlanY]

you're welcome