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.

 

[Tartesos]

After removing the *1, if the value on the 6th and or 7th caract. is a number it give the error #VALUE .... if I put the *1 back and the 6th and or 7th value is a number no problem , but if its a letter , A, B, C, etc .. the #VALUE error comes back ... any solution for this ??

Here is the formula:
=IF(ISNA(VLOOKUP(MID($B4,9,1)*1,configuration!$L$3:$M$21,2,FALSE)),"",VLOOKUP(MID($B4,9,1)*1,configuration!$L$3:$M$21,2,FALSE))

Thanks

 

[Tartesos]

anyone??

 

[Charlie45]

Sorry I couldn't get back to you right away.

One qway you might be able to solve your problem is on you lookup (E1:F200) is to enter the number in the E column as text. That way your lookup on your table is alway text whether the letter of the alphabet or number stored as text.

 

[Aladin Akyurek]

Sorry I couldn't get back to you right away.

One qway you might be able to solve your problem is on you lookup (E1:F200) is to enter the number in the E column as text. That way your lookup on your table is alway text whether the letter of the alphabet or number stored as text.

What is the version of Excel you are on?

What kind of result is the VLOOKUP bit supposed to return - text or number?

 

[Tartesos]

The excel is from 2002. And the return is always text. Any solution posible for this??
I have tried that Charlie45 but is not solving the problem.

Thanks

 

[Aladin Akyurek]

The excel is from 2002. And the return is always text. Any solution posible for this??
I have tried that Charlie45 but is not solving the problem.

Thanks

Try...

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"",
    VLOOKUP(MID($B4,9,1)+0,configuration!$L$3:$M$21,2,0),
    VLOOKUP(MID($B4,9,1),configuration!$L$3:$M$21,2,0)))

 

[Tartesos]

Yup, that is perfect Aladin ... I only removed the $ in front of the $B4 so I can drag the formula to other cells and it works 100% !! Thank you.

Do you think that I can use this formula also on new versions of Microsoft Office? ... like 2007, 2010 ect ...

Thanks

 

[Aladin Akyurek]

Yup, that is perfect Aladin ... I only removed the $ in front of the $B4 so I can drag the formula to other cells and it works 100% !! Thank you.

You are welcome.

Do you think that I can use this formula also on new versions of Microsoft Office? ... like 2007, 2010 ect ...

Thanks

Yes.

 

[Tartesos]

Aladin, you remember this formula you help me with a few weeks ago?
=IF(ISNA(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$AH$4:$AH$2488))
,"",LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$AH$4:$AH$2488))

Sometimes when the formula dont have to give an output data, because is not need it, it gives me a 0 ... a number 0. but the funny thing is that is not happening on others cells that are on the same situation that those with a 0, do you know why ???

Thanks

 

[Aladin Akyurek]

Aladin, you remember this formula you help me with a few weeks ago?
=IF(ISNA(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$AH$4:$AH$2488))
,"",LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$AH$4:$AH$2488))

Sometimes when the formula dont have to give an output data, because is not need it, it gives me a 0 ... a number 0. but the funny thing is that is not happening on others cells that are on the same situation that those with a 0, do you know why ???

Thanks

If FIND fails, this formula would give a blank (i.e., ""). If FIND succeeds and the relevant cell in data!$AH$4:$AH$2488 is empty, you'd get a 0. If you would like a blank instead of 0 in such cases, try the following somewhat modified version:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,
      FIND(data!$B$4:$B$2488,imput!B4)),
    T(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),
        data!$AH$4:$AH$2488)),"")

Note that FIND attempts an exact match... Note also that the modification makes the formula a bit more efficient.

Is this what you wanted?