Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: Match Two Cells And Return Value In Third

1. Member
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. 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 ?

3. Super Moderator
Join Date
4th July 2004
Location
Posts
2,371

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. Member
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. Member
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. Member
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,

Excel Video Tutorials / Excel Dashboards Reports

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

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