+ Reply to Thread
Results 1 to 7 of 7

How to match right numbers?

  1. #1
    Registered User
    Join Date
    03-22-2014
    Location
    London, England
    MS-Off Ver
    Excel 2012
    Posts
    4

    How to match right numbers?

    Hi guys,

    I'm new to excel at school, so I would love to ask you for little help. I have to pull all correct numbers from second sheet into first sheet at right column. I've tried to write a formula to solve the problem, the answer is not correct. The main purpose of the formula is compare the value from TASK sheet into first column in DB sheet and if find matches to continue matching with next cells on the row. The advanced level is to write all correct matches into 1 cell, delimited by comma. I will appreciate any help. thanks in advance.

    Kind regards,
    Michael.

    school_task.xlsx

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to match right numbers?

    You can try this formula. This should show correct numbers that were matched by all criteria.

    In E2 and pull formula down

    =IFERROR(INDEX(DB!$E$2:$E$1088,MATCH(Task!A2&Task!B2&Task!C2&Task!D2,INDEX(DB!$A$2:$A$1088&DB!$B$2:$B$1088&DB!$C$2:$C$1088&DB!$D$2:$D$1088,0),0)),"Not Found")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    03-22-2014
    Location
    London, England
    MS-Off Ver
    Excel 2012
    Posts
    4

    Re: How to match right numbers?

    Thank you, so much. It works perfect. This formula returns first correct match from the DB sheet. Is it possible to return all correct matches in the cell?

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to match right numbers?

    Quote Originally Posted by mi6o View Post
    Thank you, so much. It works perfect. This formula returns first correct match from the DB sheet. Is it possible to return all correct matches in the cell?
    For that you would probably need to look for a VBA solution.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to match right numbers?

    Pl see attached file with UDF ConcatenateSpecial.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-22-2014
    Location
    London, England
    MS-Off Ver
    Excel 2012
    Posts
    4

    Smile Re: How to match right numbers?

    Thanks a lot, mate! I appreciate this

  7. #7
    Registered User
    Join Date
    03-22-2014
    Location
    London, England
    MS-Off Ver
    Excel 2012
    Posts
    4

    Re: How to match right numbers?

    Hey mates,

    It's me again. I'm still working on this task for cars and got a problem. I've made some changes on UDF to make it more clear for me.
    I've split the year range, because I would love to match 'YearStart' and 'YearEnd' separately (in purpose for case[2]).

    To make it easier for testing I've filtered data just for 'A4'. I want MAKE, MODEL and SHAPE to have exact match. Examples for year:
    1. If year range in Task sheet is '1995-1999' as in 1 row and the range is in '1995-2001'. I want to pull these numbers in.
    2. If year range in Task sheet is '1995-2002' and the range is '1995-2001' in the DB sheet. Although it is out of range with 1 year, I would love to pull the numbers, too.

    Can you give me a clue or any solution?

    Please see the attached file.
    matching numbers.xlsm

    Thanks in advance.

    Regards,
    Michael

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Application.Match help. Find a match if first 4 numbers are the same. Asterisk not working
    By thelisa in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-24-2013, 08:01 AM
  2. [SOLVED] INDEX MATCH MATCH gives N/A without row numbers defined
    By cbearl78 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-01-2013, 05:18 PM
  3. Match Numbers
    By dreicer_Jarr in forum Excel General
    Replies: 5
    Last Post: 02-07-2010, 02:20 PM
  4. Replies: 4
    Last Post: 12-14-2009, 03:21 PM
  5. Match neg numbers to pos
    By colmodoc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-12-2007, 08:01 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1