Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Find & Match Number, and return the Cell

  1. #1

    Find & Match Number, and return the Cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I have two sets of data, The numbers i want to find in the range might look like:
    (Col C)
    145
    145
    145
    145
    146
    146
    147

    And from these i need it to match against the following examples:
    (Col F)
    600_145
    12345_146
    23456_152
    34567_153
    45678_154
    54789_155

    The numbers Before the underscore in cell F will change to all sorts of number/text, however, the numbers after the underscore will always be numbers, and will always have a match in in column C.

    What i need is a formula that will identify (for example) 145 is found after the _ in "600_145", and display the who cell in column X.

    The Formula goes on Sheet 4, Which has the list i want to match in Column C.
    I need the formula to look/match the value in Column C in the below lists (Found on sheet 3) and return the Cell from Col:F.

    COL:E COL:F
    (Val-Z) (Val-Y_Val-Z)
    145 600_145
    146 12345_146
    152 23456_152
    153 34567_153
    154 45678_154
    155 54789_155

    I have tried a bunch of stuff and keep coming back to:

    =IF(MATCH('Sheet 4'!C2,'Sheet 3'!E:E,0),'Sheet 3'!F2,""),

    And the problem that gives me is that i haven't told excel that COL:E and COL:F are related like that, so it is basically just reproducing the COL:F list.

    I think: Sums up where im at.

    Please help!

  2. #2
    Acolyte Azumi's Avatar
    Join Date
    Jan 2014
    Location
    Indonesia
    Posts
    29
    Articles
    0
    Excel Version
    2010
    You're explanation isn,t clear enough, would you try to upload your sample workbook?

    Thanks

  3. #3

    Post Attached Example

    Im unsure if this is the correct way to supply and attachment/example of a workbook, but here goes...

    So in "Sheet 3" i have put where i want the formula in Cell E2.

    On this workbook, there is a list of "observations" in Column C.

    In "Sheet 2" there is a list of Observation numbers in column A, and a ReferenceNumber_ObservationNumber in column B.

    What i need the Formula to do is match/lookup/find the observation number from (Sheet3-Column C), to either column in Sheet2 so it will display the relevant "Reference_Obs#" in Sheet3-Column E.
    Attached Files Attached Files

  4. #4
    I have added a sample of the workbook below.

    Thanks for your reply!

  5. #5
    Good morning,

    Use this and drag down:

    =VLOOKUP(C2,Sheet2!$A:$B,2,FALSE)

    Have a good day,

  6. #6

    Post bgoree09 is amazing!

    ExampleVer2.xlsx

    It astounded me how you could so easily answer this, like it wasn't even hard - i spent hours playing with the formula for this, and came up Nothing! hahah, Thank you so much.
    Since i was able to make sense of your answer i have now learned a bit about how Vlookup works, However i have spent 4 hours trying to replicate a similar formula.
    I really didn't want to have to come back and bug you all again, but this specific request is doing my head in.

    I have re-attached a version two of my original example, Again, its a matter of lookup/match and return:

    In Sheet 3, i want to use my newly listed data in Sheet-3 Column-E, to match/lookup the same values found in Sheet-2 Column-F, and return the appropriate value from Column-A.
    - This example is pretty much the same as my original, however, there are now other cells in the way of the data im trying to use as a range. I have tried being fancy and naming the columns as a range, and all the little things i know about using them, But for some reason i cannot get it to work due to the cells in-between the two columns in sheet two (the two columns the formula is concerned with are 'A' and 'F'.

    Thank you very much for your help guys!

  7. #7
    =INDEX(Sheet2!A:A,MATCH(C2,Sheet2!E:E,0))

  8. #8
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    To add some enlightenment to martindwilson's solution, the reason you can't use VLOOKUP() here is the array, or range, you're looking at has the return column to the left of the lookup column. VLOOKUP() is great, but it always looks in the left-most column of the array, then returns x columns to the right of that.

    Here is a great reference card from Microsoft on VLOOKUP(). It's a free download and prints on a single page, so you can hang it up in your office if you'd like.

    http://office.microsoft.com/en-us/ex...101873816.aspx

    I would, however, add some error trapping to martindwilson's formula. Since they used a 0 as the third syntax of MATCH(), it will always find an exact match. So if no exact match is found it will return #N/A error. To suppress this, use the IFERROR() formula...

    =IFERROR(INDEX(Sheet2!A:A,MATCH(C2,Sheet2!E:E,0)),"Not found")

    Replace the second parameter of the IFERROR() function to return whatever you want if no match was found.

    HTH
    Regards,
    Zack Barresse

  9. #9

    Thanks

    Quote Originally Posted by martindwilson View Post
    =INDEX(Sheet2!A:A,MATCH(C2,Sheet2!E:E,0))
    Thank you very much!

  10. #10
    Quote Originally Posted by Zack Barresse View Post
    To add some enlightenment to martindwilson's solution, the reason you can't use VLOOKUP() here is the array, or range, you're looking at has the return column to the left of the lookup column. VLOOKUP() is great, but it always looks in the left-most column of the array, then returns x columns to the right of that.

    Here is a great reference card from Microsoft on VLOOKUP(). It's a free download and prints on a single page, so you can hang it up in your office if you'd like.

    http://office.microsoft.com/en-us/ex...101873816.aspx

    I would, however, add some error trapping to martindwilson's formula. Since they used a 0 as the third syntax of MATCH(), it will always find an exact match. So if no exact match is found it will return #N/A error. To suppress this, use the IFERROR() formula...

    =IFERROR(INDEX(Sheet2!A:A,MATCH(C2,Sheet2!E:E,0)),"Not found")

    Replace the second parameter of the IFERROR() function to return whatever you want if no match was found.

    HTH

    You guys are Out-Standing!
    Thank you all very much, the wealth of knowledge here is just phenomenal!

    Just to explain the purpose for all this:

    I have a program that spits out a CSV file with all the details of an observation, and a sheet with all the notes and specifics of the observations.
    This data then needs to be re-organized in to a very specific template (Still CSV format), so it maybe imported to another program.
    What i am building is an excel document i can just put the original data into, and have it organize all the fields for me, then copy all the outputs to a CSV file ready for import.

    As you have probably already noticed, i am less than novice when it comes to the formulas excel uses, and am very appreciative for the help i am receiving here.

    Thank you all very much!

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •