View original (French)

Excel : VLOOKUP on cell with function

BenZ -  
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   -
Hello everyone,

I want to perform the "VLOOKUP" function on a cell that contains the formula RIGHT(A1,10). The VLOOKUP function does not seem to work.

The configuration of my Excel file:
Column A: a reference of the type P0000000000 (a letter followed by 10 digits)
Column B: the function "RIGHT(A1,10)" to return only the 10 digits
Column C: The function "VLOOKUP(B1,'sheet2'!A:B,2,FALSE)"

The sheet sheet2 contains the 10-digit references without the letter.

Is it possible to perform a lookup on a part of the cell and not the entire cell? (a combination of VLOOKUP() and RIGHT() in a way)

Or can I configure the VLOOKUP function in such a way that I can perform a lookup on a cell that already contains a function?

NB: replacing FALSE with TRUE in the VLOOKUP function does not give me the correct result

Thank you very much in advance

Configuration: Windows Vista / Internet Explorer 7.0

4 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello

    it is possible that your code list in sheet2 (thus without the letter) is numeric values while RIGHT(A1;10) returns text from the reference with the letter
    Try transforming your formula in B1:
    =RIGHT(A1;10)*1 which should convert the result into a number.

    you can of course directly use the code in the VLOOKUP formula:
    =VLOOKUP(RIGHT(A1;10)*1;'sheet2'!A:B;2;0)

    also be sure, just in case, that your initial code with the letter does not contain any spaces that could interfere with the RIGHT function

    let me know if it still doesn't work

    best regards

    Let's ask ourselves if we are not the only ones who understand what we are explaining?
    3
  2. BenZ
     
    Thank you very much, it's working very well!

    Is there a function to avoid #VALUE in empty cells like with #NA and the IF(ISNA()) function?

    Is there also a way to remove the letter in display? (i.e., that the cell value is indeed P0000000000 but it only displays 0000000000)

    I can't find the option in the cell format...

    Best regards,
    0
    1. BenZ
       
      I found the answer to my first question for those interested:
      =IF(ISERROR(VLOOKUP(RIGHT(A1,10)*1,'sheet2'!A:B,2,FALSE)),"",VLOOKUP(RIGHT(A1,10)*1,'sheet2'!A:B,2,FALSE))

      I'm still looking for an answer to my second question, I can't seem to modify the display properties with text, it only seems to work with numeric values...

      Thank you in advance.
      0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello Benz and Vaucluse

    by setting the cell to number format - custom number; type
    "P"0000000000
    --
    Michel
    0
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Hello Michel

      I think it's the opposite of what Benz would want to do, if I'm not mistaken, which is to input P100000000 and not see the P... but I don't know how to do that apparently.

      On the other hand, if it's for using the search formula, indeed, displaying the P from a format like you mentioned allows searching for numerical values without using the RIGHT trick. This might be the solution for Benz.

      Best regards.
      0
    2. BenZ
       
      Thank you for your contribution, michel_m, but that’s not what I’m looking for.
      Vaucluse is right, I want to “enter P100000000 and not see the P”…

      I don’t want to use it for the search function; it’s just for aesthetics to have the references that match exactly with the listings.

      Does that seem impossible then?

      Thank you both anyway.
      0
    3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      excuse me, nap time is merciless!

      I'll look later but it's going to be expensive (image!) the aesthetics
      0
  4. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    come on, a little aesthetics in this brutal world:
    columns B1:B20 to adapt 
    Sub esthetique() For lig = 1 To 20 If Left(Range("B" & lig), 1) = "P" And Len(Range("B" & lig)) = 11 Then Range("B" & lig).Characters(Start:=1, Length:=1).Font.ColorIndex = 2 End If Next End Sub

    Michel
    0