Lookup across multiple columns

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

In the earlier topics, we used a similar dataset to this Topic Example Workbook to do a lookup for a Salesperson and return the corresponding Product Sold or Quantity sold. Provided that the salesperson name is unique this will give us the correct value. But in this data set we now have two people called James – just doing a lookup on “James” in Column A (using either a VLOOKUP or an INDEX/MATCH) will only return the first value (in row 3) which may not be what we’re after.

For this lookup to work effectively, we need to be more specific about what we’re looking up by also using the values in other columns in order make the lookup unique. For example we need to lookup not just “James” in Column A but also the department that the James that we want works in (Column B) – this will then give us a unique record to lookup against.

So if we want to find the Product Sold by James who works in the Hardware department, we can do this by combining the corresponding values in Column A (James) and Column B (Hardware) in another column (say Column E) and then doing a Lookup in this column, returning the value in the same row (Row 8) in Column C – returning the value ‘Laptop’.

This is the method we’ll have a look at in this topic. It’s important to note that we only need to use this concatenation method if the column we’re returning values from isn’t one with numbers of where we don’t want to aggregate (e.g. sum or count) the returned values. If instead of returning the ‘Product Sold’ we wanted to return the ‘Quantity Sold’ then we may be able to use the SUMIFS (From Excel 2007 onwards) instead – this function takes multiple criteria (e.g. Name, Department) and returns the sum of corresponding values in another Column (e.g. Quantity Sold).

In Column E of the Topic Example Workbook, we will have a concatenation formula which combines the values in Columns A and B. There is a function in Excel to do this – the =CONCATENATE (text 1, text2,…) function which takes cell references separated by commas and returns a text value combining them – so if in cell E2 we type =CONCATENATE(A2,B2) then this will return the value ‘AndrewDigital Sales’ which is the combination of the value in cell A2 (‘Andrew’) and B2 (‘Digital Sales’).

An alternative to using the concatenation function is by combining cell references/values using the Ampersand character (&). So instead in cell E2 we could type =A2&B2 which would give the same value of ‘AndrewDigital Sales’

We can also put a character (such as an underscore) in-between the cell references to make the combined value easier to read – for example we could do =CONCATENATE(A2,”_”,B2) or =A2&”_”&B2 which would return ’Andrew_Digital Sales’ instead.

Once we have the desired concatenation in cell E2 we drag the formula all the way down in Colum E down to cell E8, and then we can use these values in Column E to look up against.

We can now use the concatenated values in Column E to perform a lookup against. For example using the INDEX/MATCH to perform a lookup, if we wanted to find the product sold by James who works in the Hardware department we could lookup the value “James_Hardware” in Column E (Assuming we concatenated with an underscore in-between) by using the MATCH Function: =MATCH(“James_Hardware”,E:E,0) returns 7 which indicates that in Column E the text “James_Hardware” occurs in row 7. We can then combine this with the INDEX Function to get the corresponding value in Column C [Product Sold].

Our completed formula is =INDEX(C:C,MATCH(“James_Hardware”,E:E,0),1) which returns the value in cell C7 – ‘Blu-Ray’.

In the Topic Example Workbook above, carry out the following steps:

• Construct a concatenation column in Column E which combines the values in Column A and B (in the same way as above)
• Columns G and H contain the Lookup values which we want to find. Construct a concatenation lookup string in Column I by combining the values in G and H in the same way as above
• Use the INDEX/MATCH functions to lookup this concatenation lookup string in Column E and return the corresponding Product Sold in Column C
• Use the SUMIFS function to work out the quantity sold by each salesperson/department combination in columns G and H.
• A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

• To get a unique list of values across multiple columns see filtering for unique values. This will give you the values to look up and build into a concatenation lookup string.
• Use the SUMIFS formula to look up across multiple columns and return the sum of corresponding values in another column

YouTube Video: How to concatenate the contents of multiple cells in Excel 2007