Double lookup

[Guest]

Hi,

In Sheet 1, I have the following:

Employee Number Start Date Text
840320 01/01/2005 Temporary
810324 05/06/2004 Permanent
320567 02/06/2003 Manual

These are in columns A, B and C, and there are 1050 entries, which are
entered down to row 1051.

In sheet 2, in column A I want to enter an employee number, and in column B
I want to enter a start date. Then in column C I want to be able to display
the appropriate text string ONLY if there is a match for both employee and
Start Date.

So, for example, if I put in "810324" as the employee number in A1 on Sheet
2, and "05/06/2004" in B1 on Sheet 2, In C1 the formula would display
"Permanent", as there was a match.

However, if I put in "810324" in A2 on Sheet 2, and "02/06/2003" in B2 on
Sheet 2, C1 would display "No match" as there was no match.

Many Thanks,

Gary Thomson

 

[Guest]

In column C of your Sheet2, use
=IF(B1=VLOOKUP(A1,Sheet1!$A$1:$C$3,2,FALSE),VLOOKUP(B1,Sheet1!$B$1:$C$3,2,FALSE),"No Match")

 

[Roger Govier]

Hi Gary
One way
=IF(SUMPRODUCT(--(Sheet1!$A$2:$A$1051=A1),--(Sheet1!$B$2:$B$1051=B1))>0,"Permanent","No
Match")

 

[Guest]

two common ways to do this.

Use a helper column with =A1&B1
and use a lookup based on the concatination

Second use sumproduct()
=sumproduct(--(A1:A1050=number),--(B1:B1050=Date),C1:C1050)
the --( is to change a logical true false to a numeric 1,0 and the arrays in
each section must be the same size, and cannot be shorthand for entire
columns or rows.