Look for the 6th and 7th character in a cell

[Tartesos]

Hello again.

I was wondering if there is a way, using a formula, to look in a cell and depending on the carachter on the 6th and or 7th place to output a specific information in another cell.

Lets say we have a number, REF345678567.

I need to have a formula that will look that number and when it finds the number 5 (6th carac.) it will fill another cell with information from another columm.

It needs to look also for the 7th carac., in this case 6, and put information on another cell from a columm.

Can anyone help me with this ??

Thanks and regards.

 

[Charlie45]

You might have to add *1 after MID($A1,6,1) to coax it into a number.

=vlookup(MID($A1,6,1)*1,tabledata,2,false)

 

[Tartesos]

Im missing something here because is not working. I have now a columm with the 200 posible outputs.
On the cell that I want the information to be I have set this =vlookup(MID($A1,6,1),E1:E200,2,FALSE), ok?
well, on the B columm , that is where I want to final data output is showing #N/A ... what it is that Im doing wrong??
Thanks

 

[Tartesos]

You might have to add *1 after MID($A1,6,1) to coax it into a number.

=vlookup(MID($A1,6,1)*1,tabledata,2,false)

When I do what you are saying I get a #REF error ...

 

[Charlie45]

Change the E1:E200 to E2:F200

 

[Tartesos]

Change the E1:E200 to E2:F200

Ok, now the ref error is gone but all I can see is a 0 on the b1 cell ... is not picking the information form the table ...

 

[Tartesos]

is working now !!!
Thanks

 

[Charlie45]

You're welcome. Glad I could help.

 

[Tartesos]

Thank you all, that was fastttttttttttttt

 

[Tartesos]

Small step back .... when the 6th or 7th caract. is a letter like B, A or any combination of two letter .. it will not work ... showing the #VALUE .. error ... do I have to change something to also accept letters ?

 

[Tartesos]

Removed the *1 and now is ok ...