o my god .. finally i found this video .... i was literally exhausted to find a solution regarding this .... and u came to my rescue at the right time .. thank you beardo .❤❤
Thank you soooo much Chris! I've spent almost an hour trying to figure this out. All the other videos on XLookup were very unhelpful. I just followed yours and got the answer I was looking for. Video saved and subscribed!
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.
Hi Chris, This is really helpful. Thank you for this. However, do you have any idea what the formula should be if both lookup values are approximate values? Example: Temperature Values(Row): Values from 10 to 50 with increments of 1.1 and Humidity Values (Column): Values from 10 to 85 with increments of 5 - Return array is X Values What should be the formula if entered data is Temperature = 22 and Humidity = 37.5? I've tried different lookups and didn't get any luck so far.
Thanks Chris, I understand it better now, still have a question about it, Can XLOOKUP be used to look up employee birthdays and list their names and dept, etc.. in a dynamic list
This is great. Is it possible to nest if functions in an xlookup? For example, I have a spreadsheet with a bunch of names on there and may see the same Timmy Jones on the spreadsheet 6 times, but I only want the info that has a date between a certain range of dates. For example, only the February date. Could xlookup look for Timmy Jones and spit back info for the entry for February? Does that sound like something xlookup could do?
Hi Chris, formula looked simple and easy to understand. But when i use it, it does not work. Return array and lookup array is in different worksheet. Does that make any difference?
Hi, I, wondering if you can help me craft a formula. I have sheet one where columns beginning with E2 & F2 tho not all complete contain contract & delivery order # s that I want to match against columns beginning with C2 & D2 on sheet 2 (from another source) to obtain a plan # in B2 also on sheet 2. Whatever the right formula is I will just double click to have it expand to the columns I need. Is there a way to craft a formula of sheet 1 to have it use E2 & F2 values (contract & delivery order #) search array of contract # & delivery orders on sheet 2’s C2 and D2 to then give me the corresponding plan # in B2?
Chris, how can I combine this with your =TRIM(CLEAN(SUBSTITUTE(CELL,CHAR(160),"")))? I'm trying to run xlookup on data that is copied from a table in word and pasted into excel, but the formatting in the word table seems to be keeping xlooup from returning the data.
Hi Chris, still building out my tracking Xls for my Project team - I'm trying to use Xlookup to do 2 things - 1 match serials numbers in 2 sheets (1 sheet has 50 no duplications - staff sheet, and the 2nd sheet has 200+ with duplications as its a weekly time sheet) - I find all the people matching ok in col F in the staff sheet ie- =XLOOKUP(A2,'RPV Data'!B:B,'RPV Data'!A:A, "...") fyi The "..." just makes it clear no name, then where I'm stuck is if a have a new name on the time sheet I need to add to my staff sheet col F as this use else where to add their hours each week. How can I show any new names on the first empty row at the bottom of the staff sheet Col F, so then I can manual add them in to the col A, B, this will then allow them to be found.
I might suggest that you use power query. I use this application for identifying people on time entries with no permanent record in payroll yet. (If you haven't learned Power Query, it is a great New Year's resolution) You have two data sets (set up as tables). First table is staff data table. You are looking to add to the population of this data as time is incurred in the second table, your time data. (I presume name + hours). It is important to have an identifier (e.g. employee ID) for the names other than how someone enters a person's name in either of your data sets (staff/time) Using PQ, eliminate duplicate names in the time data. This is your "no dupes time data". Merge this table with your staff data table and then filter for null values. Here, you will have a table that is just null values that you can print out and add to your staff data table. Important note: It is better to have a field other than a name (e.g. employee ID) that you wish to return as names in two data sets can have all sorts of issues.
Hi Chris! Thank you so much for this. I have one question here. I am trying to return blank if error. I put [," "] after the formula you showed in this video but it does not work... Is there other way to return blank for error?? For example if you put "Mike" in name column, #N/A would come back in your example but I would like to get blank on that. Thank you so much!!
Is it possible to have one piece of criteria look into two separate columns and if there is a match in either of the columns it returns the correct value
Hi Chris, one question. I have this kind of table. I'm searching a formula to bring the number in column B based on a number in column A and word apple. Would you have a formula to solve this? 123456 987654 red apple 123456 654321 yellow apple 123456 321987 pink apple 456789 876543 red apple 456789 765432 yellow apple 456789 524163 pink apple
@@ChrisMenardTraining thanks but this is not working. Let me explain in another words. I would like with a vlookup or xlookup with criteria the number A and criteria *red* to get number from column B associated to column A and word red. Is this clearest ?
o my god .. finally i found this video .... i was literally exhausted to find a solution regarding this .... and u came to my rescue at the right time .. thank you beardo .❤❤
Most welcome 😊
You just saved our company dozens of hours with this simple little formula. THANK YOU!!!
Glad to help. Thanks, Deidra.
Thank you soooo much Chris! I've spent almost an hour trying to figure this out. All the other videos on XLookup were very unhelpful. I just followed yours and got the answer I was looking for.
Video saved and subscribed!
This formula was simply too simple to think about. Thanks a lot. Tried hours myself before I found your site.
I was about to go towards Index Match from XLookup for the same issue. Didn't know it has nested feature. Saved my day!
Xlookup is amazing. Thank you!
Thank you Chris! Your videos are quick effective and clear!
Simplest video I found on this. Took this and applied to a massive sheet. Thanks
I'm glad it worked. Thank you, Fraser.
This is just what I needed. What an awesome feature. So easy to use. So easy to make work!
Really straight forward, this helped me solve a complex problem without lots of calcs. many thanks
Thank you, John.
Very comprehensive for beginners and novices.
Thank you!
Excellent example. It worked for me transforming cross-sectional data to panel data
Absolute lifesaver! very well explained, worked perfectly :)
Great to hear! Thanks, Heath.
This is the best tip so far for Xlookup, thank you, Sir.
That's exactly what i was looking for, thanks Chris!
Glad I could help!
This is a very helpul tutorial. Thanks!
You're welcome!
u are making life easier for me. thank you so much
Happy to help!
This video was very helpful and saved me so much time! Thank you!
I'm so glad! Thanks, Travis.
Wow index and match using xlookup you are a genius
The simple explanation, love it
Chris you are the GOAT!!!! Thank you so much!
Extremely helpful. Thank you
You're welcome!
Thankyou so much for this!! Very helpful
You're so welcome!
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.
Thank you so much! Great explanation - worked perfectly
Simple and clear. Thanks.
Can we use =xlookup(h2&h3,a2:a4&b1:e1,b2:e4)? What's the difference
Thank you! very well explained!!!
You are welcome!
you sir just earned a new sub, thank you
Awesome, thank you!
Thank you sir, Cristal clear
You are most welcome
Well explained. thank you for sharing :)
My pleasure!
Hi Chris,
This is really helpful. Thank you for this. However, do you have any idea what the formula should be if both lookup values are approximate values?
Example: Temperature Values(Row): Values from 10 to 50 with increments of 1.1 and Humidity Values (Column): Values from 10 to 85 with increments of 5 - Return array is X Values
What should be the formula if entered data is Temperature = 22 and Humidity = 37.5? I've tried different lookups and didn't get any luck so far.
Thanks Chris, I understand it better now, still have a question about it, Can XLOOKUP be used to look up employee birthdays and list their names and dept, etc.. in a dynamic list
Yes. I would use the Employee ID.
Thank you! I was looking for a way to do this.
Glad I could help!
You can also use an ampersand to add additional criteria instead of nesting another xlookup.
Fantastic. Thanks, Chris.
Glad you enjoyed it!
Thanks. Very useful!
I appreciate the positive feedback. TY
This is great. Is it possible to nest if functions in an xlookup? For example, I have a spreadsheet with a bunch of names on there and may see the same Timmy Jones on the spreadsheet 6 times, but I only want the info that has a date between a certain range of dates. For example, only the February date. Could xlookup look for Timmy Jones and spit back info for the entry for February? Does that sound like something xlookup could do?
Perfect and succinct. Thanks.
Hi Chris, formula looked simple and easy to understand. But when i use it, it does not work. Return array and lookup array is in different worksheet. Does that make any difference?
Life saver! Thanks!!
Happy to help!
Wonderful video
Thank you! Cheers!
thank you chris this is very2 helpful for me
Im taking the information from another sheet, is that a problem, can xlookup do that, its not working for me
very helpful, thanks for you
You're welcome!
Can you do this formula if the data is on a different sheet?
Hi, I, wondering if you can help me craft a formula.
I have sheet one where columns beginning with E2 & F2 tho not all complete contain contract & delivery order # s that I want to match against columns beginning with C2 & D2 on sheet 2 (from another source) to obtain a plan # in B2 also on sheet 2. Whatever the right formula is I will just double click to have it expand to the columns I need.
Is there a way to craft a formula of sheet 1 to have it use E2 & F2 values (contract & delivery order #) search array of contract # & delivery orders on sheet 2’s C2 and D2 to then give me the corresponding plan # in B2?
Chris, how can I combine this with your =TRIM(CLEAN(SUBSTITUTE(CELL,CHAR(160),"")))? I'm trying to run xlookup on data that is copied from a table in word and pasted into excel, but the formatting in the word table seems to be keeping xlooup from returning the data.
How would you write the same formula using index and match?
I need help. I have first name, last name and ID needed in another workbook. The workbooks have a different amount of rowa.
Hi Chris, still building out my tracking Xls for my Project team - I'm trying to use Xlookup to do 2 things - 1 match serials numbers in 2 sheets (1 sheet has 50 no duplications - staff sheet, and the 2nd sheet has 200+ with duplications as its a weekly time sheet) - I find all the people matching ok in col F in the staff sheet ie- =XLOOKUP(A2,'RPV Data'!B:B,'RPV Data'!A:A, "...") fyi The "..." just makes it clear no name, then where I'm stuck is if a have a new name on the time sheet I need to add to my staff sheet col F as this use else where to add their hours each week. How can I show any new names on the first empty row at the bottom of the staff sheet Col F, so then I can manual add them in to the col A, B, this will then allow them to be found.
I might suggest that you use power query. I use this application for identifying people on time entries with no permanent record in payroll yet. (If you haven't learned Power Query, it is a great New Year's resolution)
You have two data sets (set up as tables). First table is staff data table. You are looking to add to the population of this data as time is incurred in the second table, your time data. (I presume name + hours). It is important to have an identifier (e.g. employee ID) for the names other than how someone enters a person's name in either of your data sets (staff/time)
Using PQ, eliminate duplicate names in the time data. This is your "no dupes time data". Merge this table with your staff data table and then filter for null values. Here, you will have a table that is just null values that you can print out and add to your staff data table.
Important note: It is better to have a field other than a name (e.g. employee ID) that you wish to return as names in two data sets can have all sorts of issues.
Great!
TY
The Kratos of Excel
Thank you!
Awesome! Thank you
You're welcome! XLOOKUP is awesome
Hi Chris! Thank you so much for this.
I have one question here.
I am trying to return blank if error. I put [," "] after the formula you showed in this video but it does not work...
Is there other way to return blank for error?? For example if you put "Mike" in name column, #N/A would come back in your example but I would like to get blank on that.
Thank you so much!!
Hi there, try this =IFERROR(XLOOKUP(H2,A2:A4,XLOOKUP(H3,B1:E1,B2:E4)),"") It should work.
Thank you SIR😊😊😊😊😊
Most welcome
Question : how can i derive results for list of lookup_values in one row looking in list of values in column ?
Helped me pass a coursera course.
Excellent. Thanks, Kevin!
Hey, what can i do if i have a multiple rows for the same name and i want to spill all of them.
Thanks
What if I have two lookup values, and lookup array in four column, how we will apply formula ?
you made it easy
thank you
You're welcome
Any idea how i can create a criteria for numbers in xlookup?
Thank You
You're welcome
Great 🤘🤘
thank you
Is it possible to have one piece of criteria look into two separate columns and if there is a match in either of the columns it returns the correct value
Basically is it possible to have two lookup arrays
Wow thanks
Welcome 😊
Hi Chris, one question. I have this kind of table. I'm searching a formula to bring the number in column B based on a number in column A and word apple. Would you have a formula to solve this? 123456 987654 red apple
123456 654321 yellow apple
123456 321987 pink apple
456789 876543 red apple
456789 765432 yellow apple
456789 524163 pink apple
use =Countif(range,"*apple*") to do a wildcard search on the word apple.
@@ChrisMenardTraining thanks but this is not working. Let me explain in another words. I would like with a vlookup or xlookup with criteria the number A and criteria *red* to get number from column B associated to column A and word red. Is this clearest ?
Tks!
Welcome.
It didn't work for me, both of my look up arrays are columns.
Where is the Excel file to apply on it?
sir but I follow all but it's not calculate all
cute!
Thank you.
Cuter than you
Content starts at 1:28
Ok how to match with between two dates in xlookup in this show me example
Worst possible place to inject a "BTW," but other than that, thanks!
Video is so blur. Nothing is visible
Plays fine for me.
change the resolution
Thank you, that was so helpful.
You're welcome!