Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Match Two Cells And Return Value In Third

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      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, 22:38.

      Comment


      • #4
        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?

        Comment


        • #5
          Re: Match Two Cells And Return Value In Third

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

          Comment


          • #6
            Re: Match Two Cells And Return Value In Third

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

            Much thanks,

            Adnan

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X