Lookup multiple criteria in rows or columns - Xelplus - Leila Gharani (2023)

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).

Lookup multiple criteria in rows or columns - Xelplus - Leila Gharani (4)

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:

  1. Click on cell I26.
  2. Go to the FORMULAS
  3. 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:

(Video) Advanced Excel Index Match (3 Most Effective Formulas for Multiple Criteria)

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.

Video and Workbook

Feel free to Download the Workbook HERE.

Here’s Part 1 of this guide on INDEX & MATCH.

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

LEARN MORE

37 Comments

  1. Neil R SanchezDecember 17, 2017 at 7:13 am

    Hi 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.

  2. KamaruddinJanuary 27, 2018 at 4:47 pm

    Very sweet n excellent teaching of excel..

  3. Boubacar dialloFebruary 1, 2018 at 10:18 pm

    tremendous! Mrs Leila Gharani
    I really enjoyed your video.

  4. Robert WardMarch 8, 2018 at 7:48 pm

    Question. 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?

  5. Stephen GuyeMarch 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.

    • ShokJuly 13, 2018 at 12:51 am

      Hi Stephen, I have the same issue. Did you manage to find solution? Please let me know.

  6. SamuelMarch 28, 2018 at 10:29 am

    Your style of presentation is extremely good. Easy to grasp and attractive, too. Keep it up, ma’am!

    • Leila GharaniMarch 29, 2018 at 9:47 am

      Thank you! Glad you like the tutorials :)

  7. Sonya Ann MeinertJune 11, 2018 at 1:51 am

    Very helpful and easy to understand thank you thank you thank you

    • Leila GharaniJune 11, 2018 at 12:30 pm

      You’re very welcome Sonya. Glad you find it helpful.

    • Leila GharaniJune 12, 2018 at 7:15 am

      Thank you! I’m glad to hear that :)

  8. MarcoJune 11, 2018 at 8:37 pm

    I 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.

  9. SalionJune 30, 2018 at 8:08 am

    Hi 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.

  10. DeeSeptember 4, 2018 at 1:59 am

    thank 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

  11. DeeSeptember 4, 2018 at 2:04 am

    thank 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

  12. Rosemberg CaicedoSeptember 4, 2018 at 10:11 pm

    Hi 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 CaicedoSeptember 4, 2018 at 10:13 pm

      sorry, 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

  13. AdamSeptember 19, 2018 at 9:59 am

    Hi
    I just love the way you explain !!!
    AMAZING

  14. AshishOctober 18, 2018 at 6:03 pm

    Hi. 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 GharaniOctober 23, 2018 at 8:44 am

      Hi Ashish, Thank you! Yes Index works for columns as well.

  15. anuNovember 28, 2018 at 4:34 pm

    Hi 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 GharaniNovember 29, 2018 at 7:52 am

      Hi – 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)).

    • cocoMay 27, 2019 at 3:32 am

      I 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,

  16. ROBIN HANSCOMBEMarch 6, 2019 at 9:05 am

    Hi 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 SmedleyApril 7, 2019 at 8:11 pm

      Thank 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

  17. JulioMarch 18, 2019 at 3:52 pm

    Your 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
  18. Scott MetlenApril 28, 2019 at 11:03 pm

    here 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 SmedleyApril 30, 2019 at 10:54 pm

      Thank 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

  19. Peter SchulzeMay 11, 2019 at 11:05 am

    Thanks 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 GharaniMay 16, 2019 at 4:35 am

      Hi Peter – the download includes both versions. The basic first and if you scroll down the complex version too.

  20. AndreMay 15, 2019 at 2:52 pm

    Hi, 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 CITLY1U13CX7602799CITLY1U3

    So 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

    • ChrisAugust 23, 2019 at 8:18 pm

      Thank 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.

  21. C. MorenoMay 20, 2019 at 6:15 am

    Hi, 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?

  22. MattMay 28, 2019 at 3:20 am

    Absolute Lifesaver. Thankyou so much

  23. SebJune 7, 2019 at 1:53 pm

    file for part 2 is the same as file for part 1 and missing al lthe additional tabs.

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)
  1. Step1: Create a column with unique strings for the lookup value. Insert a column to the left of your dataset like this: ...
  2. Step2: VLOOKUP formula for multiple values. Now we can insert a VLOOKUP formula to return all the matching results for our lookup value:
15 Jul 2021

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
  1. In the first row, select all the cells to be populated (B2:C2 in our example).
  2. Type the formula and press Ctrl + Shift + Enter. ...
  3. Drag down the formula to the remaining rows.
6 Sept 2022

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
  1. Type the formula in the first cell, press Ctrl + Shift + Enter, and then drag it down to a few more cells.
  2. 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.
30 Sept 2022

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:
  1. Select cell C3 and click on it.
  2. Insert the formula: =VLOOKUP(B3,$E$3:$F$7,2,0)
  3. Press enter.
  4. 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...
  1. SUMPRODUCT. FORMULA : =SUMPRODUCT((B3:B10=D12)*(C3:C10=D13)*(D3:D10))
  2. SUM (ARRAY FORMULA) FORMULA : =SUM((B3:B10=D12)*(C3:C10=D13)*(D3:D10)) ...
  3. SUMIFS. . ...
  4. INDEX-MATCH. FORMULA : ...
  5. INDEX-MATCH (Version 2) FORMULA : ...
  6. LOOKUP. FORMULA : ...
  7. 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]
  1. Select a cell in your data set.
  2. Press CTRL + T (shortcut for creating an Excel Defined Table).
  3. A dialog box appears, press with left mouse button on the checkbox if your data set contains headers.
  4. Press with left mouse button on OK button.
14 Apr 2021

Videos

1. Find Multiple Match Results in Excel (Easier Solution For ALL Excel versions)
(Leila Gharani)
2. How to use Excel Index Match (the right way)
(Leila Gharani)
3. Excel VLOOKUP: Basics of VLOOKUP and HLOOKUP explained with examples
(Leila Gharani)
4. Excel Formula Lookup Multiple Results: Return headers based on matrix values
(Leila Gharani)
5. Excel LOOKUP Function Only PROS Use (simple to complex examples)
(Leila Gharani)
6. Excel 3 Ways to Lookup within Boundaries: SUMIFS, SUMPRODUCT & INDEX
(Leila Gharani)
Top Articles
Latest Posts
Article information

Author: Fredrick Kertzmann

Last Updated: 02/06/2023

Views: 5841

Rating: 4.6 / 5 (66 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Fredrick Kertzmann

Birthday: 2000-04-29

Address: Apt. 203 613 Huels Gateway, Ralphtown, LA 40204

Phone: +2135150832870

Job: Regional Design Producer

Hobby: Nordic skating, Lacemaking, Mountain biking, Rowing, Gardening, Water sports, role-playing games

Introduction: My name is Fredrick Kertzmann, I am a gleaming, encouraging, inexpensive, thankful, tender, quaint, precious person who loves writing and wants to share my knowledge and understanding with you.