**The Complete Guide to INDEX & MATCH**

** CHAPTER 2:**

Some reports involve the need to find a value from a source table using multiple criteria in rows and columns. In this example, we have a table containing both the actual and budget revenues and profits for each application as shown below:

From this data, you need to create a report that returns the value corresponding to three criteria that the user selects:

- Actual or Budget
- Revenue or Profit
- App name

This becomes the matrix lookup, however, unlike the regular cases, this has more than one header (Row 20: Actual or Budget, Row 21: Revenue or Profit).

## Index Match approach

When using the Index Match approach, the first thing you identify is the map or the area that contains the answer. Since this report needs to display the corresponding number from the criteria, the array used is C22:F31. The function at this point is written as:

Cell I22 = INDEX(C22:F31…

Had it required to display a value other than the numbers, you will need to include the cells containing those values as well. For example, if you also need to display the Division, the array becomes A22:F31 instead of C22:F31, since A21:A31 contains the Division values.

For the next argument in the INDEX() function, you need to determine how many rows you need to go down. The MATCH() function is used to find at what row number the lookup value is found. Since we want to use the selected App in cell H22 and matching it with cells B22:B31 which contains the Apps, the function now becomes:

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31…

There are three options for the match type argument:

- 1 = less than
- 0 = exact match
- -1 = greater than

Since we want an exact match, we use 0:

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0)

Find the column that corresponds to both the criteria selected in cells I20 and I21. Another MATCH() function can be used here. Unlike the regular case, your lookup value is derived from two cells. Combine these criteria using the & symbol.

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21…

The lookup array for this lookup value is found at C20:F21, which has two rows instead of one.

Initially, you would think about using C20:F21 as your lookup array and write the formula as:

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F21,0))

However, instead of displaying the value corresponding to the criteria, it results to an error. This is because MATCH() can only handle a single row or column and cannot handle a combination of rows and columns, such as C20:F21 because it will not know in which direction to move. Had it been a single row such as C20:F20 or C21:F21, it would have worked out fine.

There are multiple ways to resolve this. One way to do it is to revise the table by separating it into two—one containing only the Actual values, and the other containing only the Budget values, and then doing a VLOOKUP to change the source table array depending on the selected criteria. However, it is also possible to resolve this problem without changing the format of the current table. There are three ways to do it.

## Method 1: Using helper cells

Since the MATCH() function can only handle single rows and columns, the simplest way to resolve the problem we had earlier is to use helper cells that combine the values in rows 20 and 21 into one row instead of two. This combination becomes the unique identifier of each column.

Cell C19 = C20&C21

You will see that C19 now contains ActualRevenue. Drag this formula to the right until cell F19.

Go back to the last MATCH() function that used cells I20 and I21 as the lookup value. Instead of using C20:F21 as the lookup array, you can now use the new helper cells C19:F19.

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C19:F19,0))

This formula displays the value that corresponds to the selected criteria in cells I20, I21 and H22.

## Method 2: Using CTRL + SHIFT + ENTER (CSE)

There is another approach that eliminates the use of helper cells. This involves generating an array for the MATCH() function by pressing the keys CTRL + SHIFT + ENTER (CSE).

The MATCH() function is not meant to handle array functions, rather, it looks at things one cell at a time instead of holding things in memory and handle them. Some functions that can handle arrays are:

- INDEX()
- SUMPRODUCT()
- VLOOKUP()
- HLOOKUP()
- AGGREGATE()

However, clicking on CSE on a MATCH() function enables it to handle arrays. Write the same function as above, only instead of using the helper cells, revise the last MATCH() function to combine cells C20:F20 and C21:F21 using the & symbol.

Cell I26 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F20&C21:F21,0))

After pressing **ENTER**, you will notice that it results to an error because there is no instruction telling the MATCH() function to keep the values in memory. To see the step-by-step calculation of the function resulting to the error:

- Click on cell I26.
- Go to the
**FORMULAS** - Click on
**EVALUATE FORMULA**.

You will see the function written on the white space. Each click on the **EVALUATE** button will show you the calculation step-by-step. It first finds the value of cell H22, and finds the row containing that App, followed by finding the values in cells I20 and I21 and combining them. You will notice that the value returned by C20:F20 and C21:F21 are #VALUE! The function notices that you are trying to combine things and it is confused because it does not know how to handle such instances.

Click on **CLOSE** to exit the window. Go back to cell I26. Click on the function in the formula bar and instead of pressing the **ENTER** key, click **CTRL + SHIFT + ENTER**.

Notice that it places { } at the start and end of the function and becomes:

Cell I26 {= INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F20&C21:F21,0))}

It now displays the value that corresponds to the three criteria selected.

Click on cell I26 again and go to **EVALUATE FORMULA** to see how this differs from the previous one. Instead of returning #VALUE! for both C20:F20 and C21:F21, it is now able to find the values in those cells and combine them. The { } gives an instruction to the MATCH() function to keep the values in memory, which makes it easier for it to take C20 and combine it with C21 to give ActualRevenue, D20 with D21 to give ActualProfit, and so on.

It then finds the column number that corresponds to the criteria, “BudgetRevenue”, which is 3. The INDEX() function is now able to find the value using the numbers from the row and column arguments.

This approach is a simple way of writing but it can be confusing for a lot of people. It is only suitable when you are writing the formulas for your own use. Otherwise, if you have other users, it is very likely that those users do not know array functions. They might see the array, click on the formula, inspect it a bit, and press **ENTER**. Since they press **ENTER** instead of **CSE**, the formula will then result to an error again. In such cases, it would be best to avoid this approach.

## Method 3: Using two INDEX() functions

The third approach does not require helper cells nor CSE, but replaces the last MATCH() function with an INDIRECT() function.

Start off using the same functions for the most part except for the last MATCH() function:

Cell I29 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21…

In order to avoid the use of CSE, we need to use a function that can handle arrays. The INDEX() function can be used by putting the entire lookup area inside the INDEX() function. To do this, replace the last MATCH() function in Method 2 with:

INDEX(C20&F20&C21:F21,…

It now becomes:

Cell I29 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,INDEX(C20:F20&C21:F21…

It is important to comply with the syntax of the INDEX() function. The row_num argument is mandatory. Instead of leaving the formula up until C21:F21, you need to specify a row number. In this case, you want to tell the function to take every single row. There are two ways to do that:

- Use the Excel separator (,) and leave it empty

Cell I29 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,INDEX(C20:F20&C21:F21,)…

- Use 0 as the row number

Cell I29 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,INDEX(C20:F20&C21:F21,0)…

Next, comply with the syntax of the MATCH() function where you need to specify the match type. Again, use 0 for an exact match. The final formula becomes:

Cell I29 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,INDEX(C20:F20&C21:F21,0),0))

This gives you the same result as the first two methods. There are other ways to resolve this problem but I find these three to be the simplest ways.

**Excel Dashboards that Inform & Impress**

### Use these techniques in your own reports

#### Unbeatable value!

LEARN MORE

## 37 Comments

**Neil R Sanchez**December 17, 2017 at 7:13 amHi Leila, I like your videos a lot, I developed a macro using Index Match for Radiology in Stanford Healthcare…

I currently involved in a big project and I want to know if it is possible to open one instance of Excel if the workbooks belongs to my macro, but if is NOT then open a different instance…. in another words it is like I have two independent Excels:

One for my macro (it allows only the files that I open using my macro), and the…

Second Excel only for the workbooks I try manually open that do not belongs to my macro.Any comment I will really appreciate… Sorry but this one is the only way I found to ask you this concern.

**Kamaruddin**January 27, 2018 at 4:47 pmVery sweet n excellent teaching of excel..

**Boubacar diallo**February 1, 2018 at 10:18 pmtremendous! Mrs Leila Gharani

I really enjoyed your video.**Robert Ward**March 8, 2018 at 7:48 pmQuestion. In the example using two criteria, one budget and the second revenue I would like to get a result if one of the results is left empty. I am looking for a dual AND OR condition where if I leave budget or revenue empty I get a result on one of those matches, but also to perform as it does in your example when both revenue and budget have values. But if you empty revenue or budge the result fails. Is this possible with a composite concept like this?

**Stephen Guye**March 20, 2018 at 7:13 pm(Video) Return Multiple Match Results in Excel (2 methods)I have a lookup that I Have been trying for several weeks now, trying index,match,if,and without success.

Have I asked to much from Excel?I need to have 4 possible peramiters.

1. Serial number to a certain value (number) (i.e. 1202841)

2. Model number (text) (i.e. BPME7BA-VM)

3. Frequency Value (number) ( = to or > a given value) ( In 1 of 6 columns) (i.e. >=88.1 =90.3 =95.3 =98.3 =101.3 =104.7 <=107.9),

4. Loss value (number) (i.e. -45.74)Return value (4) to helper cell. This helper cell will then auto populate another worksheet.

**Shok**July 13, 2018 at 12:51 amHi Stephen, I have the same issue. Did you manage to find solution? Please let me know.

**Samuel**March 28, 2018 at 10:29 amYour style of presentation is extremely good. Easy to grasp and attractive, too. Keep it up, ma’am!

**Leila Gharani**March 29, 2018 at 9:47 amThank you! Glad you like the tutorials :)

**Sonya Ann Meinert**June 11, 2018 at 1:51 amVery helpful and easy to understand thank you thank you thank you

**Leila Gharani**June 11, 2018 at 12:30 pmYou’re very welcome Sonya. Glad you find it helpful.

**ikefresh**June 11, 2018 at 3:37 pmI love your teachings, you made it so so simple

**Leila Gharani**June 12, 2018 at 7:15 amThank you! I’m glad to hear that :)

**Marco**June 11, 2018 at 8:37 pmI have the following formula (two index functions with indirect functions) – two header lookup. I tested the formula within a single sheet without indirect function and the formula works. However, when in a template and I reference a separate sheet via the Indirect function, the last Index results in a #REF.

Without Indirect Function

=INDEX(CV203:DG258,1,MATCH(CV3&CV4,INDEX(CV3:DG3&CV4:DG4,0),0)))With Indirect Function

=INDEX(INDIRECT(“‘”&$A9&”‘!”&$D$94&””),B$4,MATCH(B$5&B$6,INDEX(INDIRECT(“‘”&$A9&”‘!”&$C$94&$B$94&””,0),0))), but my last index results in #REF.In advance, thank you for your feedback.

**Salion**June 30, 2018 at 8:08 amHi Leila,

I am trying to use index & match but with 3 criteria in 3 columns and only 1 criteria in the first row (table header). In the top row I have these: Year, City, Job Area, Turnover,Training Expense, Cost per Hire. Then I have 3 different years, 4 cities, and 5 job areas for each city. The turnover, training expense and cost per hire are all values that I need to lookup with an index function or another useful function.

The idea is to bring the values in a prep table with a dropdown, and then create a dynamic graph. I have followed your videos of how to do this, and can do this if there is just one row and one column… but I have 3 columns and I can’t figure out how to do it.

**Dee**September 4, 2018 at 1:59 amthank you for posting

Question: instead of a single value from the table, what is the formula to provide a dynamic list from this table, example the output is all the productivity apps and their budget profits in order of highest to lowest???

thanks in advance

**Dee**September 4, 2018 at 2:04 amthank you for posting

(Video) How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 Examples)Question: instead of a single value from the table, what is the formula to provide a dynamic list from this table, example the output is all the productivity apps and their budget profits in order of highest to lowest???

or what about a list of all apps that had an actual profit greater than their budget profit???thanks in advance

**Rosemberg Caicedo**September 4, 2018 at 10:11 pmHi Leila, your web page is great.

I have a question, I’m using index and Match as this: (=IFERROR(IF((+INDEX(Table447,MATCH(Table45[[#Headers],[P1]],Table447[#Headers],0),MATCH([@[Parse_01]],Table447[Column117],0)))>0,1,””),””))

but is not giving me the right answer in the outcome, also when I have the header location in the answer table it doesn’t even gives me an answer.

could you give me any suggestion

thanks for your help

**Rosemberg Caicedo**September 4, 2018 at 10:13 pmsorry, forgot to write that as I change the match criteria(1,0,-1) it gives me different answers or different match answers that are correct, but misses other ones.

thanks again

**Adam**September 19, 2018 at 9:59 amHi

I just love the way you explain !!!

AMAZING**Ashish**October 18, 2018 at 6:03 pmHi. Your explanations are really easy to understand. I have seen many of your lessons and have done good progress. Although I am working with a spreadsheet where I need to exicute this column wise, does index works with column as well?

**Leila Gharani**October 23, 2018 at 8:44 amHi Ashish, Thank you! Yes Index works for columns as well.

**anu**November 28, 2018 at 4:34 pmHi thanks for this however it doesn’t work with multiple column instead of muliple rows. Are you able to post a revised version for this. The below index match seems to be the only solution for multi columns using {}

=INDEX(C2:G$6,MATCH($B$14&$B$15,$A$2:A6&B2:B6,),MATCH($B$16,$C$1:$G$1,0))

**Leila Gharani**November 29, 2018 at 7:52 amHi – you will need to switch the arguments around, like you’ve done, except also make sure you look for any exact match. For a non-cse version you can use this formula: =INDEX(C2:F$11,MATCH($B$14&$B$15,INDEX($A$2:A11&B2:B11,),0),MATCH($B$16,$C$1:$G$1,0)).

**coco**May 27, 2019 at 3:32 amSee AlsoJim Crow Laws - Definition, Examples, Cases9 Things Your Auditor HatesBaseline Survey: Definition, Types, + [ Questionnaire Example]Top 20 Most Visited Countries In The World 2022I tried to use your formula, but I couldn’t figure out how to get anything to drag.

There are about 1500 rows. I want to drag this formula by row and column, but can sometimes get it to work by row. When I click it to go down by column, I get the same answer for every column.

Everything I try gives an error or it disappears (leaves a blank cell). Sometimes, I get answers in column P or Q or R that shouldn’t appear, it should be blank.

I have duplicates in columns E,G and N. I want E and G to look up the answer that’s in N. I have column F, but I don’t need it for the formula. Every time I try to move it or any column, excel tells me that some cells are fixed and then it crashed.

IF($O25IFERROR(INDEX($M$16:$M$1318,AGGREGATE(15,6,(ROW($M$16:$M$1318)-ROW($M$16)+1)/($E25&$G25=$E$16:$E$1318&$G$16:$G$1318),COLUMNS($O$16:P25))),””),IFERROR(INDEX($M$16:$M$1318,AGGREGATE(15,6,(ROW($M$16:$M$1318)-ROW($M$16)+1)/($E25&$G25=$E$16:$E$1318&$G$16:$G$1318),COLUMNS($O$16:P25))),””),””)

In case, I made it too complicated, answers should look be

Column EColumn GColumn NANS Col OANS Col PANS Col QANS Col R

Employee 1carpetsoldsoldreturned

Employee 2lampsoldsoldsoldsoldsold

Employee 2lampsoldsoldsoldsoldsold

Employee 4couchsoldsoldsoldsoldsold

Employee 1carpet

Employee 1couchreturnedsoldreturned

Employee 7couchsold – to shipsold – to shipsold – to shipsold – to shipsold – to ship

Employee 2lampsoldsoldsoldsoldsold

Employee 2bed setsoldsoldsoldsoldsold

Thank you,

**ROBIN HANSCOMBE**March 6, 2019 at 9:05 amHi there, I have a table of values (output) and fixed values in the x and y which are the width and height sizes, based upon values in the search (width and height), I am trying to get the value from the table that is the intersection between the two values, the main problem that I have is that the search values will never be the same as the fixed values on x and y and I need to search on the line/ column that is the near higher value in both cases. are you able to help with this one at all please? I hope that this makes sense

**Bryon Smedley**April 7, 2019 at 8:11 pmThank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

Microsoft Excel Tech Community

With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

XelPlus Team

**Julio**March 18, 2019 at 3:52 pmYour method 3 is awesome, thanks for it!! It really saves a lot of time and using the CSE which is annoying method, thanks for sharing this!

(Video) Lookup values across multiple worksheets: VLOOKUP / INDEX MATCH in Excel**Scott Metlen**April 28, 2019 at 11:03 pmhere is a puzzle, I have a list of different products that have various numbers of different parts for each product. I have this information in three columns, first the product name, then the part name, then then number for each product. I need to get the numbers into a matrix where the product name is the row identifier and the part is the column identifier. Any good ideas?

**Bryon Smedley**April 30, 2019 at 10:54 pmThank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

Microsoft Excel Tech Community

With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

The XelPlus Team

**Peter Schulze**May 11, 2019 at 11:05 amThanks Leila for the great content. I think the link to the Download the Workbook on this page referes to the basic index match book not to the Part 2 one.

**Leila Gharani**May 16, 2019 at 4:35 amHi Peter – the download includes both versions. The basic first and if you scroll down the complex version too.

**Andre**May 15, 2019 at 2:52 pmHi, I have the following Dynamic table where every month the # will change depending on markets conditions. What I want to retrive is a list at the end of each month that puts the results from columns A,B and C organized by priority in a single column (1 to 9 in this case):

A B C D E F

1 # X # Y # C

2 1 CGECC1U14CT3851567CGECC1U3

3 2 CT4125108CT4125145CPGB1U3

4 7 CITLY1U13CX7602799CITLY1U3So I need to get:

A B

1 1 CGECC1U1

2 2 CT412510

3 3 CX760279

4 4 CT385156

…In total on my spreadsheet I have 7 result columns that go from 1 to 903.

What formula can I use to get the end result I need instead of having to copy/paste everything and sort it in a diferent spreadsheet everymonth. Ty

**Chris**August 23, 2019 at 8:18 pmThank you for your comment. We receive a lot of questions daily and as much as we’d like to answer all of them, it’s unfortunately not possible.

However, to make sure you get a response to your Excel query, it would be best to post your question on the Microsoft’s Tech Community Platform for Excel. This way you can get answers from other Excel experts.

**C. Moreno**May 20, 2019 at 6:15 amHi, my matrix uses a range for increments in both the column and row arrays. More specifically, rather than my axes increasing by a certain increment, they are based on ranges. So for example, my columns are labeled 660-700, 701-740, etc. and my rows are labeled 48-64, 65-76, etc.

I’d like to use this formula, but am having trouble. Any suggestions?

**Matt**May 28, 2019 at 3:20 amAbsolute Lifesaver. Thankyou so much

**Seb**June 7, 2019 at 1:53 pmfile for part 2 is the same as file for part 1 and missing al lthe additional tabs.

**Leila Gharani**June 7, 2019 at 3:06 pmSee AlsoUpwork profile examples and tips from six-figure freelancers [2022]Have, had or has been? (When to use the Perfect Tenses in English)Schools are still segregated, and black children are paying a priceGender segregation at work: "separate but equal" or "inefficient and unfair" - Equitable GrowthYes – Both versions were put in one file – the other tabs are covered inside the “Visually effective Excel Dashboards” course.

Comments are closed.

## FAQs

### Can you use Xlookup with multiple criteria? ›

One of the key new features XLOOKUP brings to the table is **the ability to lookup using multiple criteria** (without complex array formulas – arrays are now dynamic!).

**How do I VLOOKUP multiple rows at once? ›**

**VLOOKUP for multiple results (workaround)**

- Step1: Create a column with unique strings for the lookup value. Insert a column to the left of your dataset like this: ...
- Step2: VLOOKUP formula for multiple values. Now we can insert a VLOOKUP formula to return all the matching results for our lookup value:

**Can you do a VLOOKUP with 3 criteria? ›**

**No, you can't concatenate multiple return values from a VLOOKUP function**. It will only return one instance. This example shows how to concatenate multiple values using multiple conditions using the TEXTJOIN, IF and COUNTIF functions.

**How do I pull multiple rows in Xlookup? ›**

XLOOKUP to return multiple columns or rows

**You enter the formula in the top-left cell of the results range, and Excel automatically spills the results into adjacent blank cells**. In our case, the return array (B2:D7) includes 3 columns (Date, Item and Amount), and all three values are returned into the range G2:I2.

**How do I look up multiple columns with Xlookup? ›**

One advantage of the XLOOKUP Function is that it can return multiple consecutive columns at once. To do this, **input the range of consecutive columns in the return array, and it will return all the columns**. Notice here our return array is two columns (C and D), and thus 2 columns are returned.

**Can you VLOOKUP 2 columns at once? ›**

**VLOOKUP doesn't handle multiple columns**. In the following example, if we wanted to find the match for both Movie and Showtime column, it wouldn't be possible with basic VLOOKUP syntax.

**Can VLOOKUP lookup multiple columns? ›**

**The VLOOKUP function can be combined with other functions such as the Sum, Max, or Average to calculate values in multiple columns**. As this is an array formula, to make it work we simply need to press CTRL+SHIFT+ENTER at the end of the formula.

**How do I do a VLOOKUP for multiple columns at a time? ›**

**Vlookup between sheets and return multiple columns**

- In the first row, select all the cells to be populated (B2:C2 in our example).
- Type the formula and press Ctrl + Shift + Enter. ...
- Drag down the formula to the remaining rows.

**Does VLOOKUP work with multiple matches? ›**

The VLOOKUP function is designed to return only a corresponding value of the first instance of a lookup value, from a column you choose. But there is a workaround to identify multiple matches. The array formulas demonstrated below are smaller and easier to understand and troubleshoot than the useful VLOOKUP function.

**What is Xlookup vs VLOOKUP? ›**

**VLOOKUP defaults to the closest match whereas XLOOKUP defaults to an exact match**. To correct that in VLOOKUP, you have to type FALSE as your fourth argument.

### What are the 4 parameters in VLOOKUP? ›

VLOOKUP takes four arguments: **lookup_value, table_array, column_index_num, and range_lookup**. Lookup_value is the value to look for, and table_array is the range of vertical data to look inside. The first column of table_array must contain the lookup values to search.

**How many rows can Xlookup handle? ›**

Does Xlookup have a limit? The Xlookup function doesn't have a limit. This means you can use all **1,048,576 rows** and 16,384 columns of a workbook.

**How many rows can VLOOKUP handle? ›**

VLOOKUP and XLOOKUP can use all **1,048,576 rows** and 16,384 columns of a worksheet.

**How do I use Xlookup for multiple columns? ›**

One advantage of the XLOOKUP Function is that it can return multiple consecutive columns at once. To do this, **input the range of consecutive columns in the return array, and it will return all the columns**. Notice here our return array is two columns (C and D), and thus 2 columns are returned.

**How do I match horizontal and vertical data in Excel? ›**

**=MATCH()** returns the position of a cell in a row or column. Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria. For short, this is referred to as just the Index Match function.

**Can Xlookup return multiple rows? ›**

Using FILTER Instead of XLOOKUP For Multiple Results

Since **the XLOOKUP function can only return a single row or column's worth of data when it finds a match**, we will need to utilize a different function to carry out our desired result. The FILTER function allows you to condense a range of data based on criteria.

**How do I VLOOKUP multiple columns and return one value? ›**

**How to Vlookup and return multiple values in Excel**

- Type the formula in the first cell, press Ctrl + Shift + Enter, and then drag it down to a few more cells.
- Select several adjacent cells in a single column (F1:F11 in the screenshot below), type the formula and press Ctrl + Shift + Enter to complete it.

**What can I use instead of Xlookup? ›**

**XLOOKUP Google Sheets – 4 Best Alternatives!**

- METHOD 1: USING INDEX/MATCH.
- METHOD 2: USING VLOOKUP.
- METHOD 3: USING QUERY.
- METHOD 4: USING FILTER.

**Is there a better version of VLOOKUP? ›**

The superior alternative to VLOOKUP is **INDEX MATCH**. While VLOOKUP works fine in most cases, it tends to reveal flaws when you start using it in large and complex sheets. The INDEX MATCH formula is actually two different functions; INDEX and MATCH. array is range of cells or an array constant.

**Which is better INDEX match or Xlookup? ›**

Index/Match can search right-to-left or left-to-right and doesn't require you select as large an array in most cases. No matter what side of the fence you're on with that debate, **XLOOKUP seems to have outdone them BOTH**.

### How do you drag a VLOOKUP formula horizontally and vertically? ›

**To copy the formula down a column, we need to follow these steps:**

- Select cell C3 and click on it.
- Insert the formula: =VLOOKUP(B3,$E$3:$F$7,2,0)
- Press enter.
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

**Is VLOOKUP horizontal or vertical? ›**

Look up vertically and horizontally

Microsoft Excel used to have two functions for different lookup types, each having its own syntax and usage rules: **VLOOKUP to look vertically in a column** and HLOOKUP to look horizontally in a row. The XLOOKUP function can do both with the same syntax.

**How do I pull data from multiple criteria in Excel? ›**

**7 Ways to lookup a value based on multiple criteria or conditions...**

- SUMPRODUCT. FORMULA : =SUMPRODUCT((B3:B10=D12)*(C3:C10=D13)*(D3:D10))
- SUM (ARRAY FORMULA) FORMULA : =SUM((B3:B10=D12)*(C3:C10=D13)*(D3:D10)) ...
- SUMIFS. . ...
- INDEX-MATCH. FORMULA : ...
- INDEX-MATCH (Version 2) FORMULA : ...
- LOOKUP. FORMULA : ...
- IF NO MATCH FOUND.

**Can Xlookup search rows? ›**

One of the nice benefits of XLOOKUP is **it can easily return entire rows or columns as a lookup result**. This can be done also with INDEX and MATCH, but the syntax is more complex.

**How do I lookup multiple values to return multiple values? ›**

**Lookup and return multiple values [Excel Defined Table]**

- Select a cell in your data set.
- Press CTRL + T (shortcut for creating an Excel Defined Table).
- A dialog box appears, press with left mouse button on the checkbox if your data set contains headers.
- Press with left mouse button on OK button.