How to VLOOKUP in Google Sheets with Apps Script?

[@birendraprasadsah5517]

Awesome👍

[@ExcelGoogleSheets]

Thanks 🤗

[@AlberichRheinmann]

Thanks a lot for your interesting videos ... and many thanks for your wonderful presentation. You would be ( are ) a very good teacher 😊❤

[@rajeshr8887]

Interesting and well explained as always! You make learning App Scripts for dummies like me a breeze.

[@ExcelGoogleSheets]

:)

[@muhammadhaseeb6677]

Exactly what I was searching today , then just fond that you uploaded this video one day before. Great video, Keep it up. I am learning a lot from you :)

[@ExcelGoogleSheets]

:)

[@hollythyng6106]

This is a great video. Well presented, and very easy to follow. Thank you! I've subscribed

[@ExcelGoogleSheets]

👍

[@nikkiacute]

this is amazing, thank you. So well explained and easy to follow for noobs like me. And I can see you're responding to people too - really helpful! How do you ignore a blank row please? my spreadsheet is active so new rows added all the time, I'd like to run the function on rows containing data only.

[@haustimp]

This was amazing - thank you very much!

[@futuretechindian]

Very informative.

[@ExcelGoogleSheets]

Thank You!

[@AqeelRaza-b3s]

My question is that in VBA we can utilize Worksheet fomulas in script e.g. we can write down Vlookup in Script and get the values in script, and it is far more easier to get things through scripts. Is there anyway one can get such support in Google sheet, since sheet formulas are also part of Google sheet

[@Kevin_Cohen]

Amazing video, love these apps script videos!

[@anetawieczorek3360]

Thank you so much. It work, but my data are transpossed. May you please advice how this loop should look like if Sales Date would be transposed - data from Sales Rep (col C) would be places in columns from B1 to DF1 (for eg.110 positions instead of 2041) and matched values should be returned to B10 to DF10 ( or at best to first empty row ).

[@gdevelop9678]

Thank you very much sir; I finally got it working with a drop down List as desired; :) :)

[@ExcelGoogleSheets]

Excellent!

[@bigbrother1211]

Will definitely use it sometime, thanks!

[@kulvinder8211]

sir ,What should I do if I want to pick the value of 2 columns.

[@koonerhospitalitygroup9688]

see below; the example retrieves 2 columns. You just need to make sure that the number of null statements is the same as the number of columns your getting and then for your set values you need to make sure that the number of columns matches. const matchSku = searchValues.map(searchRow => { const matchRow = srcdata.find(r => r[0] == searchRow[0]) {return matchRow ? [matchRow[4],matchRow[0]] : [[null],[null]]} })

[@oneup2022]

Thank you 🙏for sharing this great knowledge. One query though, can we make this script conditional based on column label, I mean whenever we run this script it will first search for specific column label, then it will paste the matching values. I would be so grateful if some one address this query.

[@imamfachrudin7846]

can we have trigger for this? in case there's any additional data from worksheet regularly

[@johnstewart2945]

Spot on, this is so helpful. As someone new to Apps Script coding, this was a great help. How can the return be modified to skip updating (and not overlay with [null] when a match doesn't exist)? The line of code that I'd like to update to skip updating rows that don't match is currently: return matchRow ? [matchRow[7]] : null? Thank you so much!

[@sephamdan6182]

why i cant run setvalues from the result of array that have one row three column ? instead my getrange for paste it has 1 row 3 column please answer teacher.

[@martinsaldanokrogslund6238]

Really cool video, thanks a lot for uploading it!

[@ExcelGoogleSheets]

:)

[@erikaknollenberg7526]

This is excellent, thank you. I'm wondering how to do this when the sheet doesn't know that it's Column H it needs to go into. Instead, to find that it's Column H and them proceed from there?

[@denisbilo]

My god you're amazing! I was trying to do something particular. I have a spreadsheet where I have to associate an ID code in an automatic way. The script should make a research in bigquery and do a match of the name and the surname and then give me back the ID code associated with the person. Like a vlookup into BigQuery. Is that possible? Do you have a video about that? I've found nothing online...

[@jaywilner4187]

thank you so much! this will help me at work doing lookups. is there a way to do a lookup not for the entire value but say a text string (return all values that match string "ali"?

[@ExcelGoogleSheets]

Sure. I would suggest learning JavaScript String methods in order to understand how to do other types of matches, including contains.

[@lpanebr]

I wonder which use case should this be better or if it has a performance benefit?

[@ExcelGoogleSheets]

I wouldn't replace spreadsheet VLOOKUPs with this. This is necessary only when you have similar issues that you need done in scrips. Example: you have a Web App and you need to find matching price for product selected by the user.

[@lpanebr]

@@ExcelGoogleSheets hmmm yes. I'm sure I have more than a couple places I'm using a mix of filter, map or for loops that could be simplified! Thanks!

[@abnersangalang3849]

hi, on what cases is it more advantageous to use a script rather than the built in vlookup function?

[@ExcelGoogleSheets]

It should only be used if it's a part of a larger script, shouldn't be used to replace native functions.

[@abnersangalang3849]

@ExcelGoogleSheets got it. thanks

[@erniyunita1285]

Is the performance be better if we use app script?

[@mohsinkhan4232]

Hi Sir, In this video you used the Vlookup only one column if we pick the multiple columns then how to do this?

[@koonerhospitalitygroup9688]

Finally figured it out. Below gets to columns back. const matchSku = searchValues.map(searchRow => { const matchRow = srcdata.find(r => r[0] == searchRow[0]) {return matchRow ? [matchRow[4],matchRow[0]] : [[null],[null]]} }) and for the last line edit make sure that your .setValues(matchSku) is as wide as the range your getting

[@ElizabethGemmell-ss2ue]

This code does ALMOST exactly what I want it to when applied to my spreadsheet, but it doesn't automatically update when modifying or adding rows to, using your example, the "sales data" sheet. I modified your code to use dynamic ranges rather than static, but it still doesn't update without having to run the code again, which is problematic for a number of reasons. Also, I would prefer if it simply skipped rows that didn't match rather than giving an output, as those rows in my spreadsheet are non-recurring events, and are inputted manually, so I'd appreciate it if it would stop replacing those values with a 0. Any tips?

[@abnersangalang3849]

try adding an event trigger and select the option "onEdit"

[@anunayjallan]

Thanks for the video! Is there any way in which we can copy the source formatting to the lookup cell?

[@ExcelGoogleSheets]

There is but it would be really slow.

[@enriqueferolino7080]

Amazing tutorial. Explained very well.. But how to vlookup the value if the column is reverse? for example my lookup value or criteria is in the J:J column then I want to lookup in column B:B. What would be the code of that? Thank you🙏🙏

[@antonellogenco7821]

Hi, thanks for the video, very useful, what happens in case of duplicate values? I assume it takes all the values, but how can I modify the code to make the match not just one column but two columns? EX: Take all the values of column number 4 if NAME = ISABELLA and SURNAME = BRANDON? Thank you

[@JuanJose-ik8gf]

Great video, I have tried to include a match inside the search to get for each row a different column but i could not do it, I hope you can make an extension of this tutorial with that option :) thanks,

[@Anakinr13]

The .find method doesn't work, any idea? "Error TypeError: ratesData.find is not a function"

[@ExcelGoogleSheets]

That typically means that you didn't get ratesData correctly in previous lines.

[@pigeonworks8366]

Had same error. Turned out i had .getValues instead of .getValues()

[@AxelAire-yh9qq]

Hello, very nice How to, and thank for help the comuunity :) But i don't have a find method, it's possible to share you'r library or what is you'r video to explain it ? :D Thk

[@AxelAire-yh9qq]

function DOLOOKUP() { const ss = SpreadsheetApp.getActiveSpreadsheet() const ws= ss.getSheetByName("Test Drive") const wsData = ss.getSheetByName("Test Drive Result") const ratesData = wsData.getRange(6,3, wsData.getLastRow()-1,wsData.getLastColumn()-1).getValue() const searchValue = ws.getRange("F4").getValue() const matchRow = ratesData.find(r => r[0] == searchValue) console.log(matchRow) }

[@ExcelGoogleSheets]

there is no library. so long as you are under V8 engine you can use find even if it doesn't show in autocomplete.

[@Kaenshin]

I was wondering if there is a way, so it will keep the previous data if won't find instead of NULLing it? 😄 Is there something to replace NULL so he skips it and don't update it? And only update those which he finds?

[@Kaenshin]

I guess, I did and it worked 🤔'return matchRow1 ? [matchRow1[3]] : [searchRow1[2]]'

[@ExcelGoogleSheets]

👍

[@gdevelop9678]

This is so awesome and almost similar to what I've been struggling to do without success... Instead of populating the rate in all corresponding rows, how (which is what I'm trying to do) will you code it if it's a dropdownList in Sales Data sheet? I succeeded to modify your code to getValue from a dropdown selection and it works when I run the function from the script editor; but the onEdit fonction won't populate the corresponding value on dropdown selection... If you can please help with this... Again, thx...

[@ExcelGoogleSheets]

I'm not sure where you're getting your data. For example onEdit simple trigger doesn't allow data coming from a different spreadsheet. Otherwise it should work if there are no restrictions. What's in your logs?

[@francoisprevot7067]

How can i return nothing insteand of "null" with the match function ? By "nothing" i mean => If you don't have any match jump to the next one (don't earase data already in the cell). With your method the script remove data and replace with "blank cell". How can i change this to keep my data already in cell ?

[@ExcelGoogleSheets]

If you remind me what the code was in this video I might be able to help you. Otherwise I don't remember 😀

[@francoisprevot7067]

@@ExcelGoogleSheets const ratesData = wsRates.getRange(9, 1, wsRates.getLastRow() - 1, 12).getValues(); const searchValues = wsSalesData.getRange("C2:C2041").getValues(); const matchingRates= searchValues.map( searchRow => { const matchRow = ratesData.find(r => r[0] == searchRow[0]) return match = matchRow ? [matchRow[7]] : [null] }); console.log(matchinRates) wsSalesData.getRange("O2:O2041").setValues(matchinRates) So my problem is on this line " return match = matchRow ? [matchRow[7]] : [null]" > If the match isn't find i don't want null that erase my datas. I want to keep it but i don't know if there is a simple way to do it or not. Also how can i setup a dynamical Rage for those : wsSalesData.getRange("O2:O2041") wsSalesData.getRange ("C2:C2041") (i don't want to run all rows every time the script run but only those where there is data)

[@johnstevens4211]

@@ExcelGoogleSheets I have the same question, hope you can help. In the line const match = matchRow ? matchRow[2] : null , what do I need to change to keep the current cell value and not change it to null if no match is found. Thanks!

[@chrishooks1714]

Same question as well. I don't want it to overwrite the data currently in the cell, here is the code similar to yours: const ss = SpreadsheetApp.getActiveSpreadsheet(); const wsPaymentData = ss.getSheetByName("Payments") const wsApprovedApps = ss.getSheetByName("Test") const paymentData = wsPaymentData.getRange(2,1,wsPaymentData.getLastRow()-1,4).getValues(); const searchValues = wsApprovedApps.getRange("C2:C1990").getValues(); const matchingPayments = searchValues.map(searchRow => { const matchrow = paymentData.find(r => r[0] == searchRow[0]) return matchrow ? [matchrow[1]] : [null] }) //console.log(matchingPayments) wsApprovedApps.getRange("J2:J1990").setValues(matchingPayments)

[@chrishooks1714]

@@johnstevens4211 Figured it out!!! You have to merge the results from the existing data and the data your filtering. I created a const called existingpayment which gets the entry for that particular column on the original data. During the filter if no match is found instead return "null" return the existing value (in my case existingpayment). Hope this helps! function matchPayment(){ //Get data from each sheet const ss = SpreadsheetApp.getActiveSpreadsheet(); const wsPaymentData = ss.getSheetByName("Payments") const wsApprovedApps = ss.getSheetByName("Approved Applications") const paymentData = wsPaymentData.getRange(2,1,wsPaymentData.getLastRow()-1,4).getValues(); const searchValues = wsApprovedApps.getRange("C2:J1990").getValues(); const matchingPayments = searchValues.map(searchRow => { const existingpayment = searchRow[7]; //extract value from existing column J const matchRow = paymentData.find(r => r[0] == searchRow[0]); //console.log(matchingPayments); return matchRow ? [matchRow[1]] : [existingpayment] //If no match is found return existing entry from column J }) //console.log(matchrow); //console.log(matchingPayments) wsApprovedApps.getRange("J2:J1990").setValues(matchingPayments) }

[@IvyPlans]

Great explanation. Is there a way to automatically run the code without having to manually run the code. If not available, can it run with a button?

[@guyoimano]

yes, you can put triggers on

[@robinnauli4891]

how to make it renewed every i edit it

[@mosintyagi5434]

map function also not coming. please suggest for both

[@ExcelGoogleSheets]

If map function is not showing then you made a mistake before that.

[@muhamadekocahyono2661]

Can we run it automatically?

[@marcosrs75]

man you're good

[@ExcelGoogleSheets]

Thank You!

[@Puner54]

Teacher...some of your AppsScript work and some dont???

[@ExcelGoogleSheets]

Not sure what you mean by "dont". Any particular examples of the ones that don't work?

[@Puner54]

@@ExcelGoogleSheets Teacher...I couldn't make either of the Timestamp Scripts work. Went through them with a Fine Toothed Comb too???

[@ExcelGoogleSheets]

@@Puner54 Share the code you've tried, maybe we can help to spot the problem.

[@Puner54]

@@ExcelGoogleSheets Teacher...I'm not certain on how to "Share" the Code? Don't I need an Email Address to do so?

[@ExcelGoogleSheets]

Paste it in comments

[@trandanhlong5659]

Can you share your file in your video ?

[@ExcelGoogleSheets]

gist.github.com/chicagocomputerclasses/b89812449243cb0d79229f4eebcd999a

[@trandanhlong5659]

@@ExcelGoogleSheets Thank you very much

[@GangaRam-bt7li]

Please give solution to repeat selected rows on every print . Thanks

[@learningtechie2964]

This guy never shares his codes :(

[@ExcelGoogleSheets]

Yea, this guy sucks! Get him! :)

[@Dfjjiii8btrdcvvbbhhhhhhhhh]

How can i contact you... Urgent?!!!!!