hostdigi.blogg.se - How to use vlookup in excel on two spreadsheets

#How to use vlookup in excel on two spreadsheets how to

Hence, this formula creates 2 columns of data – One column has the unique identifier and one has the score. It’s an array where a comma represents next cell in the same row and semicolon represents that the following data is in the next column. Using array formulas instead of helper columns saves you worksheet real estate, and the performance can be equally good if used less number of times in a workbook.Ĭonsidering the same data set as used above, here is the formula that will give you the result:

VLOOKUP with Multiple Criteria – Using the CHOOSE Function So here is the non-helper column method for you. Some people may prefer to not use any helper column while using VLOOKUP with multiple criteria. Instead of having 4 columns in the table array, I could manage with only 2 columns. I did it as it makes me use less number of cells in the VLOOKUP function. In fact, if you don’t want to temper with the original data, that should be the way to go. Why did I insert the helper column between column B and C and not in the extreme left? – There is no harm in inserting the helper column to the extreme left.But if you use a separator, then even the combination would be different (D2 and D3).

Note that while A2 and A3 are different and B2 and B3 are different, the combinations end up being the same. Here is a very simple example (forgive me for my lack of creativity here):

Why have I used | symbol while joining the two criteria? – In some exceptionally rare (but possible) conditions, you may have two criteria that are different but ends up giving the same result when combined.

There are a couple of questions that are likely to come to your mind, so I thought I will try and answer it here: Now this lookup value is used to get the score from C2:D19. For example, the lookup value for the VLOOKUP function in G2 is Matt|Unit Test. In the VLOOKUP function used here, the lookup value was modified to $F3&”|”&G$2 so that both the lookup criteria are combined and are used as a single lookup value. We create unique qualifiers for each instance of a name and the exam.

This would create unique qualifiers for each instance as shown below.

Use the following formula in the helper column: =A2&”|”&B2.

Insert a Helper Column between column B and C.

For example, there are three Matt in the data, but there is only one combination of Matt and Unit Test or Matt and Mid-Term. This unique qualifier can then be used to lookup the correct value. It’s just that I save them for special occasions when all other options are of no help.Ĭoming back to the question in point, the helper column is needed to create a unique qualifier. I love the amazing things can be done with array formulas.

It makes it faster as compared with the array functions (noticeable in large data sets).

It makes it easy to understand what’s going on in the worksheet.

I find two significant advantages of using helper columns over array formulas: VLOOKUP with Multiple Criteria – Using a Helper Column Now there are two ways you can get the lookup value using VLOOKUP with multiple criteria. But in many cases, you are stuck with the data that you have and pivot table may not be an option. If that works for you, nothing like that.

One can argue that a better option would be to restructure the data set or use a Pivot Table. Using the VLOOKUP function to get the Math score for each student for respective exam levels could be a challenge. Suppose you have a data with students name, exam type, and the Math score (as shown below):

#How to use vlookup in excel on two spreadsheets how to

How to Use VLOOKUP with Multiple Criteria Watch Video – How to Use VLOOKUP Function with Multiple CriteriaĮxcel VLOOKUP function, in its basic form, can look for one lookup value and return the corresponding value from the specified row.īut often there is a need to use the Excel VLOOKUP with multiple criteria.