Combine VLOOKUP and SEARCH in Excel
Solved
Sydounet
-
Xav -
Xav -
Hello everyone!
I have a problem using the VLOOKUP function combined with the MID function.
I have a column with product references like BG1002 (in A1 as an example).
However, I only need the 3rd character of this reference (a digit in position 1 and 3), hence the use of the MID function.
This digit corresponds to a matrix of two columns and 3 rows that I will use for the VLOOKUP function. It is of the type
C1 D1
1 product1
2 product2
3 product3
But when I do =(VLOOKUP(MID(BG1002, 3, 1), C2:C4, 2), it should return "product1", but it doesn't work!! It gives me #N/A.
It’s probably a formatting issue because when I enter the number to be searched "manually" without the MID function, it works fine. But even when I try clicking on cell format and setting the same thing in the lookup matrix and in my reference column, it still doesn't work... :(
Thanks for your help!!
Configuration: Windows Vista / Internet Explorer 7.0
I have a problem using the VLOOKUP function combined with the MID function.
I have a column with product references like BG1002 (in A1 as an example).
However, I only need the 3rd character of this reference (a digit in position 1 and 3), hence the use of the MID function.
This digit corresponds to a matrix of two columns and 3 rows that I will use for the VLOOKUP function. It is of the type
C1 D1
1 product1
2 product2
3 product3
But when I do =(VLOOKUP(MID(BG1002, 3, 1), C2:C4, 2), it should return "product1", but it doesn't work!! It gives me #N/A.
It’s probably a formatting issue because when I enter the number to be searched "manually" without the MID function, it works fine. But even when I try clicking on cell format and setting the same thing in the lookup matrix and in my reference column, it still doesn't work... :(
Thanks for your help!!
Configuration: Windows Vista / Internet Explorer 7.0
=VLOOKUP(VALUE(MID(A1,3,1)),C1:D4,2,FALSE)
Have a good day
Thanks a lot