How to compare multiple fields in Excel

1
votes

I have two spreadsheets that I would like to compare multiple columns in. How do I use Vlookup or some other kind of formula to get the correct result?

Sheet 1 has the following information.


Sheet 2 has the following information; and column H is the expected results column.

I would like to be able to input a formula into sheet 2 to pull in the line number from sheet 1 (Column A) that matches the same description and charge number from sheet 1.

3
excelvlookup
it is always nice to have an example, provide a link to a screenshot of your data and the expected results you are looking for. - Marcel
but when you accept an answer to the question by checking the green mark below the count votes to the left of that answer, then what is the point of clarifying here. You gotta remove that check mark so that we would know that this question is not answered yet. - Marcel
Sorry I'm still new to this. I tried the three suggestion from this post but none of them seemed to work. I probably wasn't specific enough in my original question. I will revise shortly. - user333
please revise your question then. - Marcel
Provided formulas don't work because you don't have matches in your data. For example, "Information Technology Specialist" won't match "INFORMATION TECHNOLOGY SPEC/CS" (not because of the case). - Yan F.

3 Answers

1
votes

Try this as an array formula (press Ctrl+Shift+Enter instead of just Enter when entering the formula):

=INDEX(Sheet1!$A$2:$A$7,MATCH(D2&A2,Sheet1!$B$2:$B$7&Sheet1!$C$2:$C$7,0))
0
votes

Supposing that you are putting this formula in the column E in SHEET 2 then :

=INDEX(Sheet1!$A:$A,MATCH(1,(Sheet1!$B:$B=Sheet2!$D2)*(Sheet1!$C:$C=$A2),0))


put this in E2 and then since this is an array formula, so press ctrl+shift+enter to calculate the formula. then copy and drag down the formula to find the other results.

0
votes

I'll show an alternative way to the array function. That should work just fine, but it is calculation-intensive because it looks at all values in both criteria columns to find what you're looking for. For a few hundred rows this is fine, but if you are using hundreds of thousands of rows then you may see performance issues. Array functions can be expensive.

The first thing I would do is make a helper column that combines the Charge number and Description fields into one field on Sheet1. This will be the value you look up to get the information you want from Sheet1. In order to use VLOOKUP this would need to be the left-most column in the table you are looking up into (the table in sheet1). Then I use a VLOOKUP formula on Sheet2 to retrieve the relevant information from Sheet1.

  • On Sheet1, right-click the column A header and select Insert (this will add a new column to the left)
  • In this new column A, type the formula =CONCATENATE(B2,C2) (assuming your data starts on the second row, accounting for headers in the first row. If not use =CONCATENATE(B1,C1)). This will combine the values from columns B and C.
  • On Sheet2, enter the following formula into whichever cell you want to display the Line Number from Sheet1: =VLOOKUP(CONCATENATE(A2,D2),Sheet1!$A:$D,2,FALSE) and fill this formula down

If you want you can hide that column we made in step 1 by right clicking the column header and then Hide.

Now, for an explanation of the magic (VLOOKUP):

The first argument is the value that you want to find. In this case, we want to find the combined charge number + description because those are the values in our helper column we created.

The second argument is saying "Look in Sheet1, columns A through D". This is the table containing information that you want to look up and retrieve from.

The third argument says "If there is a match in column A (remember this is our helper column that we made by combining charge number + description) then return the value in the second column from the left"

The final argument of FALSE means find me an exact match to the value I am looking up, not an approximate one.

There is a step-by-step illustration of this method that may come in handy as well.