excelindexingexcel-formulavlookupexcel-match

How to lookup a value based on two columns (column values are not unique)

This is my data:

File1
Name School     Age    Weight
Jack St John    15
Jack St Mary    14
Jack St Michael 12
Mary St John    16
Mary St Mary    12
Mary St Michael 15

Raw data

Name School Weight
Jack St John    80
Jack St Mary    75
Jack St Michael 95
Mary St John    75
Mary St Mary    65
Mary St Michael 80

I want to fetch Weight values referring Raw data.

I tried with MATCH and INDEX, however I kept on getting #VALUE!.

Any ideas what to use to fetch these Weight values?

Solution

  • The conventional solution is to use a helper column to make the values unique. So for example in your Raw data insert a column C with =A1&"|"&B1 copied down to suit, then in File 1, D2: 

    =VLOOKUP(A2&"|"&B2,'Raw data'!C:D,2,0)

    copied down to suit.