CODEWITHSUNDEEP
excelexcel-formula
Urvah Shabbir
Urvah Shabbir

Reputation: 985

EXCEL- Return Value of 3rd Column After Matching Multiple Columns In Two Sheets

Sheet1:

FID YEAR    Type
1   2009    #N/A
1   2010    #N/A
1   2011    #N/A
1   2012    #N/A
1   2013    #N/A
1   2014    #N/A
1   2015    #N/A
1   2016    #N/A
3   2008    #N/A
3   2009    #N/A
3   2010    #N/A
3   2011    #N/A
3   2012    #N/A
3   2013    #N/A
3   2014    #N/A
3   2015    #N/A
3   2016    #N/A

Sheet2:

fID Year    Type
3   2014    DP
3   2012    DP
3   2011    DP
4           CU
4           CU
4           CU
4           CU
6   2013    CU
6   2013    CU

IF [FID+Year] in Sheet1 matches [fID+Year] in Sheet2, I want to copy Type Value from Sheet2 to Sheet1.

What I have tried till now using StackOverflow Q/A: [for row 2, the pattern repeated for remaining rows]

=INDEX(Sheet2!A:D,MATCH(1,(Sheet2!A:A=A2)*(Sheet2!B:B=B2),0),3) gives #N/A

=INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!$A:A&Sheet2!$B:B,0),1) gives #N/A

Upvotes: 0

Views: 245

Answers (1)

Douille
Douille

Reputation: 68

In sheet 1 and 2, insert a column to the left of Column A. In that column, add a composite key like =B2&C2 for both sheet.

This groups the value of both column together and makes it easier to lookup for it.

In sheet 1's "Type" column, add =IFERROR(VLOOKUP(A2,Sheet2!$A$1:$D$10,4,FALSE),"") if the top cell and drag it down. A2 is a cell from the composite key column, Sheet2!$A$1:$D$10 is the range of Sheet 2's table.

Upvotes: 2

Related Questions