Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Match Two Cells And Return Value In Third

  1. #1
    Join Date
    12th August 2006
    Posts
    36

    Match Two Cells And Return Value In Third

    I am trying to match up data in two cells and then return the value in a third

    Cell1 Cell2 Cell3(Return Value)
    acc. pos
    12345 1111 a
    12345 5555 b
    55555 2222 c


    Match these cells to above anywhere in an array and return value in aboce cell 3 if found.

    Cell1 Cell2 Return here
    acc. pos
    12345 1111 a
    12345 7777 leave blank

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748

    Re: Match Two Cells And Return Value In Third

    Can you add a column that concatenates the two values and the lookup the concatenated value ?
    D

    Better a bad day on the water than a good day in the office

  3. #3
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,239

    Re: Match Two Cells And Return Value In Third

    Here's another way...

    Assuming that A2:C4 contains the data, E2 contains the first criteria, and F2 contains the second criteria, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

    =INDEX($C$2:$C$4,MATCH(1,IF($A$2:$A$4=E2,IF($B$2:$B$4=F2,1)),0))

    Alternatively, you can use the following formula instead, which will leave the cell blank when no match is found...

    =LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($C$2:$C$4,MATCH(1,IF($A$2:$A$4=E2,IF($B$2:$B$4=F2,1)),0))))

    Note that this assumes that Column C contains text values.

    Hope this helps!

    Edit: Removed &"" towards the end of the formula since it looks like Column C contains text values.
    Last edited by Domenic; September 15th, 2006 at 22:38.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th August 2006
    Posts
    36

    Re: Match Two Cells And Return Value In Third

    I tried it and it works great for rows that contain only numbers in both cells, but the ones with alpha and other non numeric characthers don't work.

    Is there any way around this?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th August 2006
    Posts
    36

    Re: Match Two Cells And Return Value In Third

    Didn't see Domenic's reply. Will try that.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th August 2006
    Posts
    36

    Re: Match Two Cells And Return Value In Third

    This works great!!! Am I glad I check out Ozgrid.

    Much thanks,

    Adnan

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Match Cells & Shift Rows Down If No Match
    By dtwk in forum EXCEL HELP
    Replies: 10
    Last Post: March 17th, 2011, 01:50
  2. Replies: 5
    Last Post: July 1st, 2008, 14:00
  3. Return Values Which Match Values From Other Cells
    By nkartchner in forum EXCEL HELP
    Replies: 1
    Last Post: November 12th, 2006, 15:59
  4. match 3 cells, average cell based on match and copy
    By diverdls in forum EXCEL HELP
    Replies: 7
    Last Post: September 10th, 2005, 13:38
  5. return value after lookup match
    By loglog58 in forum EXCEL HELP
    Replies: 4
    Last Post: December 1st, 2004, 23:22

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
  •  
porno