Excel XLOOKUP Function Definitive Guide - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Shawna
I'm having an issue where if the lookup_value is blank, I still get a nonsensical response to my XLOOKUP. If I force in a number on the lookup_value that is missing, I get the proper if_not_found message but blanks all come back with a response, which will not work for me. Is there any way to force blanks to NA?
So for my example, I'm having Xlookup evaluate a reported phone number (lookup_value), find it within columns that have a list of phone numbers (lookup_array), and have it return the proper fax number (return_array) associated with the phone number reported. If the phone number reported is blank, it gives me some fax number, even though it should be an error. If I force a phone number that is clearly an error (for example -1-) in the lookup value, it returns a proper -not found.- Clearly, blanks are breaking my syntax and I cannot allow it to do this, as it will impact my work. Any suggestions?
reply
I'm having an issue where if the lookup_value is blank, I still get a nonsensical response to my XLOOKUP. If I force in a number on the lookup_value that is missing, I get the proper if_not_found message but blanks all come back with a response, which will not work for me. Is there any way to force blanks to NA?
So for my example, I'm having Xlookup evaluate a reported phone number (lookup_value), find it within columns that have a list of phone numbers (lookup_array), and have it return the proper fax number (return_array) associated with the phone number reported. If the phone number reported is blank, it gives me some fax number, even though it should be an error. If I force a phone number that is clearly an error (for example -1-) in the lookup value, it returns a proper -not found.- Clearly, blanks are breaking my syntax and I cannot allow it to do this, as it will impact my work. Any suggestions?
reply
Andries
If you have two -Helmets listed under -Accessories- Excel will return the fist item listed on the array. I have this problem where I have to return multiple values when matching two values in individual columns within in the same array. To explain my predicament using your table structure, and I hope this makes sense, the first will have: Category (Accessories) - Product (Helmets) - -Added Product Category Column - (Size - like 'XL'), the second row may have something like: Category (Accessories) - Product (Helmets) - -Added Product Category Column - (Size - like maybe 'XXL') an so on. You will have to combine xlookup with additional arguments for this particular problem to return the correct values in the array.
reply
If you have two -Helmets listed under -Accessories- Excel will return the fist item listed on the array. I have this problem where I have to return multiple values when matching two values in individual columns within in the same array. To explain my predicament using your table structure, and I hope this makes sense, the first will have: Category (Accessories) - Product (Helmets) - -Added Product Category Column - (Size - like 'XL'), the second row may have something like: Category (Accessories) - Product (Helmets) - -Added Product Category Column - (Size - like maybe 'XXL') an so on. You will have to combine xlookup with additional arguments for this particular problem to return the correct values in the array.
reply
Simon
Got a question. What if I wanted to search for a value in multiple non continuous columns and return they adjacent cell. For example, look up a value in a1 and look up array in b1 to b5 and return c1 to c5 but also do the same for a second lookup array in e1 to e5 with return f1 to f5. So if value from a1 is found in b OR e it will return whats in c or f. Hope that makes sense.
reply
Got a question. What if I wanted to search for a value in multiple non continuous columns and return they adjacent cell. For example, look up a value in a1 and look up array in b1 to b5 and return c1 to c5 but also do the same for a second lookup array in e1 to e5 with return f1 to f5. So if value from a1 is found in b OR e it will return whats in c or f. Hope that makes sense.
reply
Matthew
Absolutely loving this XLOOKUP - question on example 5 - when using the XLookup to sum within a date range, could you use a SUMIF, instead of the SUM to further add to the formula the ability to define what Products you wanted to sum within that dynamic date range? I've been trying, and it gives a number as a result, but not the number I'm expecting.
reply
Absolutely loving this XLOOKUP - question on example 5 - when using the XLookup to sum within a date range, could you use a SUMIF, instead of the SUM to further add to the formula the ability to define what Products you wanted to sum within that dynamic date range? I've been trying, and it gives a number as a result, but not the number I'm expecting.
reply
Geoffroy
Great video in general, thank you. I would just point something if I may. When you do your example of summing the sales in between dates, this example is only valid if your data is sorted in ascending order. I would personally never use an XLOOKUP for this. SUMIFS is much more reliable and would not be dependent on the sorting of the data.
reply
Great video in general, thank you. I would just point something if I may. When you do your example of summing the sales in between dates, this example is only valid if your data is sorted in ascending order. I would personally never use an XLOOKUP for this. SUMIFS is much more reliable and would not be dependent on the sorting of the data.
reply
DOUGLAS
Hi Mynda - again, an outstanding video! I've been using XLOOKUP for awhile now, but seeing your demonstrate various tasks that this new function can perform will enhance my Excel skill set. Thank you again for all your wonderful instructional videos! Your the best!!
reply
Hi Mynda - again, an outstanding video! I've been using XLOOKUP for awhile now, but seeing your demonstrate various tasks that this new function can perform will enhance my Excel skill set. Thank you again for all your wonderful instructional videos! Your the best!!
reply
Qazi
Your grip on how you explain various components of Excel is amazing. Keep up the good work. Am really interested in your trainings but they are priced with the Western income levels in mind :-) (Being from Pakistan makes them a touch out of reach for us)
reply
Your grip on how you explain various components of Excel is amazing. Keep up the good work. Am really interested in your trainings but they are priced with the Western income levels in mind :-) (Being from Pakistan makes them a touch out of reach for us)
reply
Imran
When I use XLOOKUP for one column, it works fine. But, when I try it for more than one column, as in Example 4, it returns #VALUE! Any ideas as to why this might be happening. A quick response would be highly appreciated. Thanks.
reply
When I use XLOOKUP for one column, it works fine. But, when I try it for more than one column, as in Example 4, it returns #VALUE! Any ideas as to why this might be happening. A quick response would be highly appreciated. Thanks.
reply
Enoch
Great tutorial Mynda. This certainly makes life a lot easier. By the can the XLOOKUP be used to return one value in a cell? For example, say -4 blackberries to 1- but you want just the 4 to the left or just the 1 at the end.
reply
Great tutorial Mynda. This certainly makes life a lot easier. By the can the XLOOKUP be used to return one value in a cell? For example, say -4 blackberries to 1- but you want just the 4 to the left or just the 1 at the end.
reply
gary
XLOOKUP should save me a lot of time, since I transfer several columns of data.
One challenge, how do we get the fill handle to work on the first formula?
In my Office 365, cannot double click to fill the column. Ideas?
reply
XLOOKUP should save me a lot of time, since I transfer several columns of data.
One challenge, how do we get the fill handle to work on the first formula?
In my Office 365, cannot double click to fill the column. Ideas?
reply
Add a review, comment
Other channel videos
