Can you vlookup multiple cells

IFERROR function And finally, you wrap the formula in the IFERROR function to handle possible errors, which are inevitable because you cannot know how many matches will be returned for this or that lookup value, and therefore you copy the formula to a number of cells equal to or greater than the number of possible matches. Not to scare your users with a bundle of errors, simply replace them with an empty string blank cell.

Please notice the proper use of absolute and relative cell references in the formula. Putting all this together, we get the following generic formulas to Vlookup multiple values in Excel:. In case you want to return multiple values in rows rather than columns, change the above formulas this way:.

Where n is the column number of the first cell where the formula is entered minus 1. In this example, the formula is input in cells E2:H2. You already know how to Vlookup for multiple values in Excel based on one condition. But what if you want to return multiple matches based on two or more criteria? Taking the previous examples further, what if you have an additional Month column, and you are looking to get a list of all products sold by a given seller in a specific month?

So, you can just take the formulas discussed in the two previous examples, and have them check multiple conditions as demonstrated below. This layout may be useful for creating a dashboard, e. If you want to pull multiple values based on multiple criteria sets, you may prefer the horizontal layout where results are returned in rows. In this case, use this following generic formula:. And the result can resemble this:. Basically, the formulas to Vlookup multiple values with multiple conditions work with the already familiar logic, explained in the very first example.

The only difference is that the IF function now tests multiple conditions:. The double unary operator -- coerces the logical values into 1's and 0's.

And because multiplying by zero always gives zero, in the resulting array, you have 1 only for those elements that meet all of the specified conditions. Now, you simply compare the final array with number 1 so that the ROW function returns the numbers of rows that meet all the conditions, an empty string otherwise. A word of caution. All of the multiple Vlookup formulas discussed in this tutorial are array formulas.

As such, each formula iterates through all elements of the arrays every time the source data is changed or the worksheet is recalculated. On large worksheets containing hundreds or thousands of rows, this may significantly slow down your Excel. This is how you Vlookup multiple matches in Excel using formulas. To have a closer look at the examples and probably reverse-engineer the formulas for better understanding, you are welcome to download the Excel Vlookup Multiple Values sample worksheet.

I will be upfront - I don't know a formula to Vlookup duplicates that would output multiple matches in a single sell. However, I do know a formula-free read "stress-free" : way to do this, by using two add-ins included with our Ultimate Suite for Excel. In Excel worksheet, you can apply the Vlookup function to return the matching value from one column. But, sometimes, you may need to extract matched values from multiple columns as following screenshot shown.

How could you get the corresponding values at the same time from multiple columns by using the Vlookup function? Vlookup to return matching values from multiple columns with array formula.

Here, I will introduce the Vlookup function to return matched values from multiple columns, please do as this:. Select the cells where you want to put the matching values from multiple columns, see screenshot:. Note : In the above formula, G2 is the criteria that you want to return values based on, A1:E13 is the table range you want to vlookup from, the number 2 , 4 , 5 are the column numbers which you want to return values from. Note: The other languages of the website are Google-translated.

Back to English. Log in. Remember Me Forgot your password? Password Reset. Please enter the email address for your account. It really saved me tons of time and my financials were done within a week and a half versus a month! Just do it! It's really improved my confidence in working with Excel and also just opened up a lot of opportunities that I didn't have before because I didn't even know the huge amount of things you can do with Excel.

I am saving so much time that I'm actually getting off on time and taking breaks, because I'm not just behind all the time. People are now coming to me and saying "Can you help me with this? It's been kind of a turnaround. When you don't know something you tend to go to Google to try to find it, but using MyExcelOnline, that is your first source to go to and they've been really good about keeping the training courses relevent and updated all the time.

The biggest win for me is the output of information that I'm now getting out of Excel and the time it takes to produce that information. It's so much quicker than it was when I first started using Excel, and I wish I'd had it years ago.

I would say I'm saving a day a week, just in terms of the amount of information that I'm able to process now versus before. If somebody really wants to learn a lot more about Excel and do it in a fashion where you're not just searching in the dark, then I think this is a good tool to do that.

Like anything online I'm very sceptical about memberships, and I was very hesitant to start with but I liked what I saw and I thought I'd give it a go. It's nice that people can come to me and say ' Michele can you do this for me? It's helped my confidence immensely.

I think it's one of the best investments that I've ever made on myself, besides school! If you were an advanced Excel user you would be a benefit to that company and you would be adding value right from day one. So you should be learning something new every day and that was why I signed up for the academy. That's one of the best investments I've made, as far as for myself. Becoming the expert in the office, that they can come to me and be the go-to person, I think that's gonna help me down the road.