Lookup 2 criteria

[kingspur06]

Hi - I am sure this is probably simple but I cannot get my head round it....
I have a report showing selected Clients in Column A and Contact Names in Column B.
There are duplicates of Clients in column A because there can be multiple contacts per client.
But there are also duplicates of Contact Names in Column B because those contacts may have worked at multiple clients.

I then have a separate master report with my all time clients and contacts.
I am trying to find a way on the master report to identify only the records that contain the exact combination of client and contact name in the other report.
I do not know how to request Excel to look at both Client and Contact together, and match it with both the Client and Contact in the Master Report.
many thanks for your help in advance!
Mark

 

[snjpverma]

This is an array formula so you need to enter it with Ctrl+Shift+Enter instead of just pressing Enter.
Try this:

=INDEX(MasterReport!C:C,MATCH(1,(MasterReport!A:A=A2)*(MasterReport!B:B=B2),0))

Or this ?

=IFERROR(INDEX(Sheet1!$C$2:$C$1000,MATCH(A2&" "&B2,Sheet1!$C$2:$C$1000,0)),"")

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.