{"id":28894,"date":"2023-04-22T22:16:04","date_gmt":"2023-04-22T20:16:04","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=28894"},"modified":"2024-02-06T16:07:18","modified_gmt":"2024-02-06T15:07:18","slug":"xlookup-with-multiple-criteria","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-with-multiple-criteria\/","title":{"rendered":"2 Best Ways on How to Use XLOOKUP with Multiple Criteria"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/D43C852A-3967-41F8-92FA-948211FE27CA.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-29204 size-large\" title=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/D43C852A-3967-41F8-92FA-948211FE27CA-1024x576.png\" alt=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/D43C852A-3967-41F8-92FA-948211FE27CA-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/D43C852A-3967-41F8-92FA-948211FE27CA-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/D43C852A-3967-41F8-92FA-948211FE27CA-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/D43C852A-3967-41F8-92FA-948211FE27CA.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>Have you ever had difficulty<strong> locating a particular value in a large dataset<\/strong> or <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-excel-templates-and-spreadsheets\/\" target=\"_blank\" rel=\"noopener\" title=\"spreadsheet\" data-wpil-keyword-link=\"linked\">spreadsheet<\/a>? If so, then the <strong>XLOOKUP<\/strong> function is here to make your life easier!<\/p>\n<h4>XLOOKUP is an Excel function that<strong> allows you to search for a value in a range or table and return a value<\/strong> from another column. XLOOKUP can be used to find exact matches, the closest match, XLOOKUP with multiple criteria, and more.<\/h4>\n<p>It is a <strong>contemporary and adaptable replacement for older functions such as VLOOKUP, HLOOKUP, and LOOKUP<\/strong>. Approximate and exact matching, wildcards (*?) for partial matches, and lookups in vertical or horizontal ranges are all supported by it.<\/p>\n<p>&nbsp;<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-with-multiple-criteria\/#SYNTAX\" title=\"SYNTAX\">SYNTAX<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-with-multiple-criteria\/#METHOD_1_%E2%80%93_BOOLEAN\" title=\"METHOD 1 &#8211; BOOLEAN\">METHOD 1 &#8211; BOOLEAN<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-with-multiple-criteria\/#METHOD_2_%E2%80%93_CONCATENATION\" title=\"METHOD 2 &#8211; CONCATENATION\">METHOD 2 &#8211; CONCATENATION<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-with-multiple-criteria\/#Conclusion\" title=\"Conclusion\">Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"SYNTAX\"><\/span><strong><span style=\"color: #ff0000;\">SYNTAX<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To use the XLOOKUP function, you need to know the syntax and the parameters involved. The syntax of the XLOOKUP in Excel is:<br \/>\n<strong>=XLOOKUP(<span style=\"color: #3366ff;\">lookup_value<\/span>,<span style=\"color: #339966;\">lookup_array<\/span>,<span style=\"color: #ff9900;\">return_array<\/span>,[if_not_found],[match_mode],[search_mode])<\/strong><\/p>\n<ul>\n<li><strong><span style=\"color: #3366ff;\">Lookup_value<\/span><\/strong>: The value you want to search for.<\/li>\n<li><strong><span style=\"color: #339966;\">Lookup_array<\/span><\/strong>: The range of cells to search in.<\/li>\n<li><strong><span style=\"color: #ff9900;\">Return_array<\/span><\/strong>: The range of cells to return the result from.<\/li>\n<li><strong>[if_not_found]<\/strong>: Value to return if no match is found. If left blank, no matches will return #N\/A<\/li>\n<li><strong>[match_mode]<\/strong>: The type of match to perform (exact or approximate). This is an optional parameter, and the default value is 0 (exact match).\n<ul>\n<li><strong>0<\/strong> \u2013 Exact match. If none is found, return #N\/A.<\/li>\n<li><strong>-1<\/strong> \u2013 Exact match or return the next smaller item.<\/li>\n<li><strong>1<\/strong> \u2013 Exact match or return the next larger item.<\/li>\n<li><strong>2<\/strong> \u2013 A wildcard match : *, ?, and ~ have special meaning.<\/li>\n<\/ul>\n<\/li>\n<li><strong>[search_mode]<\/strong>: The direction of the search (left to right or right to left). This is an optional parameter, and the default value is 1 (left to right).\n<ul>\n<li><strong>1<\/strong> \u2013 Perform a search starting at the first item.<\/li>\n<li><strong>-1<\/strong> \u2013 Perform a reverse search starting at the last item.<\/li>\n<li><strong>2<\/strong> \u2013 Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.<\/li>\n<li><strong>-2<\/strong> \u2013 Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Now that you are familiar with the syntax of XLOOKUP, let us now explore how you can use XLOOKUP with multiple criteria.<\/p>\n<ul>\n<li><a href=\"#1\"><strong>Method 1 &#8211; Boolean<\/strong><\/a><\/li>\n<li><a href=\"#2\"><strong>Method 2 &#8211; Concatenate<\/strong><\/a><\/li>\n<\/ul>\n<p>Make sure to download this Excel Workbook and follow along to understand how to use XLOOKUP with multiple criteria:<\/p>\n<p><a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/XLOOKUP-with-multiple-criteria.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><\/span><span class=\"postTitle px-3\">XLOOKUP-with-multiple-criteria.xlsx<\/span><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"METHOD_1_%E2%80%93_BOOLEAN\"><\/span><strong><span id=\"1\" style=\"color: #ff0000!important;\">METHOD 1 &#8211; BOOLEAN<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>XLOOKUP is especially preferred because of its <strong>ability to look up multiple criteria at once<\/strong>. The best way to do that is to use XLOOKUP with multiple criteria to <strong>use the Boolean logic<\/strong>. Now you might get confused by the name, but it is simply a complicated term for a <strong>simple condition &#8211; Either true or false<\/strong>.<\/p>\n<p>In this example of XLOOKUP with multiple criteria, you want to get the price of a product that satisfies three conditions &#8211;<\/p>\n<ul>\n<li>Product Category is Pants<\/li>\n<li>Size is L<\/li>\n<li>Color is Blue<\/li>\n<\/ul>\n<p>Let us go step by step with this <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a>!<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1:<\/span><\/strong> Enter the first argument \u2013 Lookup_value. Here our lookup value is 1. This is because test arrays will return 1 only when all the criteria are met.<\/p>\n<p><strong>=XLOOKUP(1,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-157.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-28902\" title=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-157.png\" alt=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" width=\"1194\" height=\"404\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-157.png 1194w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-157-300x102.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-157-1024x346.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-157-768x260.png 768w\" sizes=\"(max-width: 1194px) 100vw, 1194px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2: <\/span><\/strong>Enter the second argument \u2013 <strong>Lookup_array<\/strong>. Here we are checking XLOOKUP with multiple criteria. We will call them our test arrays.<\/p>\n<ul>\n<li>First, the product category must be Pants. We are searching for matches of \u201cPants\u201d in the entire array A2:A13.<\/li>\n<li>Second, the size should be L. We are searching for matches of \u201cL\u201d in the entire array B2:B13.<\/li>\n<li>Finally, the color should be Blue. We are searching for matches of \u201cBlue\u201d in the entire array C2:C13.<\/li>\n<\/ul>\n<p><strong>=XLOOKUP(1,(A2:A13=G1)*(B2:B13=G2)*(C2:C13=G3),<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-158.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-28903\" title=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-158.png\" alt=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" width=\"1210\" height=\"403\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-158.png 1210w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-158-300x100.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-158-1024x341.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-158-768x256.png 768w\" sizes=\"(max-width: 1210px) 100vw, 1210px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3: <\/span><\/strong>Enter the third argument \u2013\u00a0<strong>Return_array<\/strong>. Here we are going to select the entire array that contains all the prices i.e. D2:B13.<\/p>\n<p><strong>=XLOOKUP(1,(A2:A13=G1)*(B2:B13=G2)*(C2:C13=G3),D2:D13)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-159.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-28904\" title=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-159.png\" alt=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" width=\"1283\" height=\"400\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-159.png 1283w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-159-300x94.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-159-1024x319.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-159-768x239.png 768w\" sizes=\"(max-width: 1283px) 100vw, 1283px\" \/><\/a><\/p>\n<p>Here we are <strong>creating a list of arguments<\/strong> that we want to fulfill in our search. We are looking for an exact match by <strong>using the \u201c=\u201d operator<\/strong>.<\/p>\n<p>=XLOOKUP(1,<strong>(A2:A13=G1)*(B2:B13=G2)*(C2:C13=G3)<\/strong>,D2:D13)<\/p>\n<p>The<strong> test arrays will return TRUE or FALSE<\/strong> as a result, where <strong>TRUE indicates that the condition has been met<\/strong> and similarly, <strong>FALSE<\/strong> means the <strong>condition has not been met<\/strong>.<\/p>\n<p>=XLOOKUP(1,<strong>{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}*{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE}<\/strong>,D2:D13)<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-170.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-28910\" title=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-170.png\" alt=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" width=\"1141\" height=\"478\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-170.png 1141w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-170-300x126.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-170-1024x429.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-170-768x322.png 768w\" sizes=\"(max-width: 1141px) 100vw, 1141px\" \/><\/a><\/p>\n<p>The <strong>multiplication operator will convert the TRUE and FALSE to 1s and 0s<\/strong>.<\/p>\n<p>=XLOOKUP(1,<strong>{0;0;0;0;0;0;1;0;0;0;0;0}<\/strong>,D2:D13)<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-171.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-28911\" title=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-171.png\" alt=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" width=\"969\" height=\"471\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-171.png 969w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-171-300x146.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-171-768x373.png 768w\" sizes=\"(max-width: 969px) 100vw, 969px\" \/><\/a><\/p>\n<p>We can see this in a more straightforward way also.<\/p>\n<p>Cell G1 contains the product category you want to look up in the array A2:A13. What the function will do is<strong> search for \u201cPants\u201d in every row of the array<\/strong> selected. It will then<strong> return the value 1 for every row where it finds a match<\/strong>, and 0 for no matches.<\/p>\n<p>Similarly, you enter arguments to search the required Size (mentioned in cell G2) and Color (mentioned in cell G3) in the corresponding columns B2:B13 and C2:C13.<\/p>\n<p>Finally, we are multiplying each argument using the \u201c*\u201d operator. This will ensure that the <strong>only row that returns a value of 1, meets all the required criteria<\/strong>.<\/p>\n<p>In the result, you can see that only Row 7 has TRUE matches in three columns (Lookup arrays), hence it has returned the value 1. When multiplying, only Row 7 will return the value 1, as all others will have at least one 0 in their formula, making the end result 0 as well.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"METHOD_2_%E2%80%93_CONCATENATION\"><\/span><strong><span id=\"2\" style=\"color: #ff0000!important;\">METHOD 2 &#8211; CONCATENATION<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When using boolean logic, you are testing different criteria and using TRUE or FALSE results to extract the required value. Instead, you can simply<strong> concatenate or join our lookup values and the lookup arrays<\/strong> to search for multiple criteria at once.<\/p>\n<p>You can use the <strong>Ampersand (&amp;) sign<\/strong> for concatenation<strong>. <\/strong>The &amp; sign works as a concatenation operator and <strong>clues together texts<\/strong> for different cells.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/concatenate-with-a-line-break-excel\/\" target=\"_blank\" rel=\"nofollow noopener\"><strong>Click here<\/strong><\/a> to learn three different ways to concatenate in Excel!<\/p>\n<p>&nbsp;<\/p>\n<p>Let us work on the same example and see how to use &amp; sign and get XLOOKUP with multiple criteria to work.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1:<\/span><\/strong> Enter the first argument \u2013 Lookup_value. Here our lookup value is the concatenation of the three conditions mentioned in cells G1, G2, and G3.1.<\/p>\n<p><strong>=XLOOKUP(G1&amp;G2&amp;G3,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-164.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-28907\" title=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-164.png\" alt=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" width=\"1205\" height=\"449\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-164.png 1205w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-164-300x112.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-164-1024x382.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-164-768x286.png 768w\" sizes=\"(max-width: 1205px) 100vw, 1205px\" \/><\/a><\/p>\n<p>Here we have clubbed all the criteria that we want to look up. Our three criteria are in the cells G3, G4, and G5, namely Product type, size, and color. Using the \u201c&amp;\u201d operator, we have clubbed the cells together to look up these values simultaneously.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2: <\/span><\/strong>Enter the second argument \u2013 <strong>Lookup_array<\/strong>.<\/p>\n<p><strong>=XLOOKUP(G1&amp;G2&amp;G3,A2:A13&amp;B2:B13&amp;C2:C13,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-165.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-28908\" title=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-165.png\" alt=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" width=\"1209\" height=\"437\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-165.png 1209w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-165-300x108.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-165-1024x370.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-165-768x278.png 768w\" sizes=\"(max-width: 1209px) 100vw, 1209px\" \/><\/a><\/p>\n<p>We have similarly clubbed the corresponding lookup arrays for Product Category, Size, and Color using the \u201c&amp;\u201d operator. Make sure to maintain the sequence of lookup values and corresponding lookup arrays.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3: <\/span><\/strong>Enter the third argument \u2013\u00a0<strong>Return_array.<\/strong> Finally, we have selected the Price column D2:D13 as we want to extract the price of the product.<\/p>\n<p><strong>=XLOOKUP(G3&amp;G4&amp;G5,A2:A13&amp;B2:B13&amp;C2:C13,D2:D13)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-166.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-28909\" title=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-166.png\" alt=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" width=\"1209\" height=\"429\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-166.png 1209w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-166-300x106.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-166-1024x363.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-166-768x273.png 768w\" sizes=\"(max-width: 1209px) 100vw, 1209px\" \/><\/a><\/p>\n<p>Using the &amp; sign, you can easily use XLOOKUP with multiple criteria and get the desired result.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-163.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-28906\" title=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-163.png\" alt=\"2 Easy Methods on How to Use XLOOKUP with Multiple Criteria\" width=\"965\" height=\"447\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-163.png 965w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-163-300x139.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screenshot-163-768x356.png 768w\" sizes=\"(max-width: 965px) 100vw, 965px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span><strong>Conclusion<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You can either use Boolen or &amp; sign to use XLOOKUP with multiple criteria in Excel. The concatenation approach may be easier to use, but the Boolean logic is far more flexible and powerful. You can decide which method to use based on its application and requirement.<\/p>\n<p>Please Note &#8211; XLOOKUP is available for <a href=\"https:\/\/www.microsoft.com\/en-in\/microsoft-365\/business\/compare-all-microsoft-365-business-products?ocid=cmmmq439uqo&amp;rtc=1\" target=\"_blank\" rel=\"nofollow noopener\"><strong>Microsoft 365<\/strong><\/a> users only. So, make sure to upgrade your account to use XLOOKUP with multiple criteria.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/xlookup-in-excel\/\" target=\"_blank\"><strong>Click here<\/strong><\/a> to learn how to use XLOOKUP in Excel using 11 different examples and applications.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Have you ever had difficulty locating a particular value in a large dataset or spreadsheet? If so, then the XLOOKUP function is here to make your life easier! XLOOKUP is an Excel function that allows you to search for a value in a range or table and return a value from another column. XLOOKUP can [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":29204,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[11,703],"tags":[1217,1216,1215],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/28894"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=28894"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/28894\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/29204"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=28894"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=28894"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=28894"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}