1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Cell range lookup

Discussion in 'Business Applications' started by javier1958, Oct 21, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. javier1958

    javier1958 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    5
    hello guys, I have a list of part numbers and a list of photos names as an entry i.e. 12345.jpg. Happens that the part number is the same as the photo name except with the .jpg extension.

    I need to look in the part number column see if the same exists in the photo id column and if it does I need to write the photo id name next to the part number column.

    If tried lookup, if's and last or(exact) formulas with no results.

    Has anybody encounter a similar problem ?

    JR
     
  2. jonvan

    jonvan

    Joined:
    Oct 9, 2008
    Messages:
    66
    So basically you need an additional column next to the part number column that will store the same data that is in the photo id column?

    I think I may have gotten lost in your explanation :X
     
  3. javier1958

    javier1958 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    5
    Hello, yes when the formula matches the item number with the photo id, the photo id record should be copied or moved right next to the item number cell. In summary what I need is to match the item number with its corresponding photo.

    thanks,

    JR
     
  4. jonvan

    jonvan

    Joined:
    Oct 9, 2008
    Messages:
    66
    I'm sure I'm looking at this wrong, so maybe a sample set of data would be nice, but...

    To me it sounds like you have column A that stores the part number, and in column F is the photo id. And in column B you'd also like to have photo id.

    If the part number already has a corresponding column with the photo id, why do you need an additional one?

    Sorry, but I am almost certain I'm misreading your request. If you could supply a sample set I can visually see what you need, that would help.
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Can you either describe for us the exact ranges your data is in, including all headers, or upload a sample file which reflects the ranges you are dealing with?
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Sounds like only some of the descriptions have pictures. Still, a VLOOKUP() should work for you. However, if your pictures are named 123.jpg and the item is called 123, make a copy of the picture names and paste it into another column, then do a text to columns, delimited by a period, which will give you one column of 123 and such and the other will be jpg.
    Then do your VLOOKUP() based on the new partial picture names.
    Or, if your picture names are all the same length, you could do a VLOOKUP() based on the LEFT() part of the name.
     
  7. javier1958

    javier1958 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    5
    Hello guys, thanks for the interest on helping!, a picture is worth a 1,000 words. Included here a sample file with in depth explanation.

    Hopefully this will clear the scenario,

    thanks

    Again
     

    Attached Files:

  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Check out the formula - I altered some of the codes in column B so that you would see that it works.
     

    Attached Files:

  9. javier1958

    javier1958 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    5
    thanks, this will work !!!!!!!!, there was no way I could figure out that formula.

    Regards,

    JR
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Although it looks complex, the formula is fairly simple. It consists of two parts, the latter of which is more direct to address:
    =VLOOKUP(LEFT(E2,6),B:C,2,0))
    Merely tells it to look at the 6 items from the left in cell E2 (in this case, 02-111) and then look in columns B and C for a match. If it finds a match, show what is in the second column - that is, C - for the match in B.
    (=VLOOKUP(LEFT(E2,6),B:C,1,0)) would show the text in column B).
    The 0 - which could be replaced by FALSE - at the end of the two VLOOKUP statements tells it to find only exact matches. If you had 1 (or TRUE) it would find nothing until a match, then would show that match going down until another exact match was made.
    The first part of the formula,
    =IF(ISNA(VLOOKUP(LEFT(E2,6),B:C,2,0)),""
    says IF the VLOOKUP returns a #N/A (that is, if ISNA is true) then show the value "" (nothing). If it does not return a #N/A, then show the match found by the VLOOKUP().
     
  11. javier1958

    javier1958 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    5
    thanks for the explanation !

    JR
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Glad to help - it is one of the few formulas I really understand... LOL ;)
    Please use the button at the top of the page to mark this thread as Solved.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/761426

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice