LOL I've watched this so many times. Every thing I think I remember it well enough to implement it myself I mess something up, it KINDA seems to be working, and then everything goes to hell and I just have to come back and watch again.
Can this formula be adapted to exclude "0" values in the source data? In other words, if I have a "0" present in the lookup/return array field, I would like it to ignore that cell and move on to the next one that has a value larger than "0"
hello, is there a way to apply this to google sheets? the functions xmatch is present in sheets, but trying to use it for a alphanumeric dataset doesn't seem to work. im using "=XMATCH("*?*",to_text(P:P),2,-1)" and all it does is return "1". the result should return "8" if everything's working properly. what am i doing wrong?
I would like the last value text that does not include 0.00. Is there any way to add that exception to the formula used in this video(XLOOKUP with VALUETOTEXT)? I am choosing cells B5:B57
Thanks for posting this reaaly helpful video. Is it possible to determine the last 3 columns that contain values within a row that may have several blank cells, ie columns 1 - 20 contain 5 values and 15 blank cells (value, blank, blank, value, blank, value etc). Hope this makes sense.
Yea, there may be an easier way to do it, but if nothing else, you could use a LET formula to search a range, to get the last non-blank. Then use drop so that you have restricted that range to 1 less than your first result. That will give you the second to last, repeat that again to get the third from last. Then output those 3 resulting numbers.
Is this possible on a non Excel 365 version? We only use 2007,2013 Excel version in our company Hope you can help me because this is exactly what I need. 😣😣😣
After installation of ExcelDna.Xfunction64, XLOOKUP function already appeared but not VALUETOTEXT Function and it only works in 2013 Excel version Hope you have another formula for the same purpose for 2007 & 2013 Excel version. 😣😥
I am trying to use the xmatch command in a macro to get the last row number to create a pivot table. I get an error in lr=xmatch("*",A:A,2,-1) that there is a Compile error: Expected: list separator or ) and the colon between the A's is highlighted. What should I be doing different?
XLOOKUP AND XMATCH are not available in Google Sheets at the moment, but you can get similar results using the method in this video th-cam.com/video/gY7fVMGP7Dk/w-d-xo.html
Thank you for posting! The last formula is very close to what I'm looking for, except for a number of columns I'd like the column name... Is there a way to tweak the last formula, or I'd have to use a completely different one? Many thanks for sharing!
Greate video sir..... Can you do a video for Geographical Info (Geocode: Longitude/Latitude) to Google Forms. there are no options for geolocation in the google form and also there is no add-on for it.
This will not work in Forms, at least the way Forms is designed now. You will either need a Web App or you will need to add lat/long in the spreadsheet after the address was already populated.
Huge help and really appreciate you taking the extra step to show the nuances of capturing text and numbers. Thank you!
THANK YOU! I have been struggling with trying to figure out this concept for way too many hours!
I can't show you how happy i am that you have solve me some big time. Thank-You🙏
Glad to hear that
LOL I've watched this so many times. Every thing I think I remember it well enough to implement it myself I mess something up, it KINDA seems to be working, and then everything goes to hell and I just have to come back and watch again.
Great video. Your demonstration has helped me alot with a current issue that I have in Excel. Thanks for posting !
YOU SAVE ME IN AN INTERN PROJECT!!
Worked like a charm -- worked like how "Expelliarmus" did for Harry Potter! Thank you so much!
👍
Thank you so much for sharing! It helped me a lot!
Thank You very much Sir.🙏🙏
There is no VALUETOTEXT function in Google sheets. What do I do then?
Can this formula be adapted to exclude "0" values in the source data? In other words, if I have a "0" present in the lookup/return array field, I would like it to ignore that cell and move on to the next one that has a value larger than "0"
Very useful ! You helped me with my work issue, thank you !!!
Great to hear!
hi, Can you show how the XMATCH works for numeric data instead of text
hello, is there a way to apply this to google sheets? the functions xmatch is present in sheets, but trying to use it for a alphanumeric dataset doesn't seem to work. im using "=XMATCH("*?*",to_text(P:P),2,-1)" and all it does is return "1". the result should return "8" if everything's working properly. what am i doing wrong?
I would like the last value text that does not include 0.00. Is there any way to add that exception to the formula used in this video(XLOOKUP with VALUETOTEXT)? I am choosing cells B5:B57
XLOOKUP is not available in Excel 2016 and Excel 2019...
Thanks for posting this reaaly helpful video. Is it possible to determine the last 3 columns that contain values within a row that may have several blank cells, ie columns 1 - 20 contain 5 values and 15 blank cells (value, blank, blank, value, blank, value etc). Hope this makes sense.
Yea, there may be an easier way to do it, but if nothing else, you could use a LET formula to search a range, to get the last non-blank.
Then use drop so that you have restricted that range to 1 less than your first result. That will give you the second to last, repeat that again to get the third from last. Then output those 3 resulting numbers.
Is this possible on a non Excel 365 version?
We only use 2007,2013 Excel version in our company
Hope you can help me because this is exactly what I need. 😣😣😣
Thanks ALOT... I need this
Thank you so much!
After installation of ExcelDna.Xfunction64, XLOOKUP function already appeared but not VALUETOTEXT Function and it only works in 2013 Excel version
Hope you have another formula for the same purpose for 2007 & 2013 Excel version.
😣😥
Something like this should work if you apply the formula with ctrl+shift+enter
=MAX(IF(A1:A1000="","",ROW(A1:A1000)))
I am trying to use the xmatch command in a macro to get the last row number to create a pivot table. I get an error in lr=xmatch("*",A:A,2,-1) that there is a Compile error: Expected: list separator or ) and the colon between the A's is highlighted. What should I be doing different?
I get the same error using the xlookup as well.
In macros do this th-cam.com/video/NrYDAEsYcbU/w-d-xo.html
@@ExcelGoogleSheets I had originally tried that one and I get an error. This is what I have lr = Cells(Rows.Count, 1).End(xlUp).Row
@@mikejpaulus Where is your data located?
What does the error say?
How to get Date by using this formula?
Can you send me the google sheet format of my excel sheet
I don't understand the question
Very nice video👍
Thank you very much!
how to show last filled cell value in a userform vba excel
Very nice video 👍. Is there something similar in google sheets? I believe XMATCH AND XLOOKUP do not work on google sheets.
XLOOKUP AND XMATCH are not available in Google Sheets at the moment, but you can get similar results using the method in this video th-cam.com/video/gY7fVMGP7Dk/w-d-xo.html
Thank you for posting! The last formula is very close to what I'm looking for, except for a number of columns I'd like the column name... Is there a way to tweak the last formula, or I'd have to use a completely different one? Many thanks for sharing!
by column name, I actually mean table column name (not column "A" for example)... thank you!
Good for Excel but "VALUETOTEXT" function is unknown to Google sheets. I use =INDEX(B2:B,COUNTA(B2:B)) but you can't have blank rows with this.
Use TEXT(val,"@") instead of VALUETOTEXT
Greate video sir..... Can you do a video for Geographical Info (Geocode: Longitude/Latitude) to Google Forms. there are no options for geolocation in the google form and also there is no add-on for it.
This will not work in Forms, at least the way Forms is designed now. You will either need a Web App or you will need to add lat/long in the spreadsheet after the address was already populated.
@@ExcelGoogleSheets Thanks for your response ..!
Many thanks.
Nice 🙏🙏
Thank you! Cheers!
Thank you
👍
first non-empty comment
:)
Thanks a lot. L O V E
en español:
=COINCIDIRX("*?*";VALORATEXTO(B3:B37665);2;-1)