18:15 In excel, once you are used to the Index Match style of making functions you just take the two criterias their relevant ranges and multiply those. You will get back an array with the values from the rows where both criterias are true. What you are doing here his essentialy the same, but with structured referencing it is way simpler to understand in excel. If you specify the ItemType and the ItemSize in named cells, for example "Jacket" and "Large", and you have a table called Items with columns for Type, Size and Price you could solve it like this: =Index(Items[Price], Match(1, (ItemType=Items[Type]) * (ItemSize=Items[Size]), 0)) This is so much clearer and it says exactly what it is doing. This could ofcourse be exapanded to three, four, five etc criterias by expanding the middle section where the True/False of the criterias are multiplied.

@johanlarsson9805

2 жыл бұрын

I see that you got there in the end anyway! Also, great for begginers that you took the time to show what the array calculations resulted in so that it is clear why the multiplacation actually works. You've more than earned my Like.

@Glamador

2 жыл бұрын

I was curious about how your suggested syntax would work, so I tried it. Unfortunately, I cannot get it to function at all. Could you help me to understand? When I use a colon (:) as you describe, the resultant array is just a grid of all cells between the lookup table and the named cell. Multiplying those two grids together only produces a grid of #VALUE errors (where excel tries to multiply the text values) and zeroes (for each of the interconnecting blank cells in between). The only way I know to get the intended output is using a boolean comparison before multiplying, to eliminate the text strings e.g. INDEX(Items[Price], MATCH(1,(Items[Size]=ItemSize)*(Items[Type]=ItemType),0)) Can you help me spot what's going wrong?

@johanlarsson9805

2 жыл бұрын

@@Glamador You are ofcourse entirely correct, and sorry for my misstake having caused you to waste time. I have fixed my comment as to not lead others astray :) Thanks for informing me!

That's a great explanation. For clarity, I would typically create a commission table with the reps as vertical axis and regions as horizontal. Then write a match function like this: =match(R2,{"placeholder", "Atlanta", "Denver", "Minneapolis" ...},0). Then validate the results to the table. Finally, build your vlookup using the match function for the column argument. Or, use a nested lookup if there are lots of territories.

"Have you ever opened a formula and it looked like this..." Me: *Vietnam flashbacks*

This is F'in MAGIC! Especially that money shot in the commission rate column!

I think making a helper column using name and region for the commission rates and then using vlookup with array formula would make the process a bit easier..still a great video though 👍

@ExcelGoogleSheets

2 жыл бұрын

Thanks!

@thehawk40375

2 жыл бұрын

@@ExcelGoogleSheets please share practice file for this video kzread.info/dash/bejne/fJthyMVxirXWdsY.html

@davidvansickle9545

2 жыл бұрын

Yep, when I need to do a lookup with data from multiple columns, I'll create a ”key" column with the relevant data appended together. So for this the commission table would include something like "=region + sales rep" and the vlookup would look like "=vlookup(region + sales rep, ,1,0)"

@brightmatter

2 жыл бұрын

figured I'd say this, but looks like you did it first. GJ

@mattshu

2 жыл бұрын

@@thehawk40375 you can’t just follow the video?

My knee jerk formula for the last one would have been a query. I love seeing new ways to look at things.

@ExcelGoogleSheets

2 жыл бұрын

:)

Great video as always. For the last example I had a similar problem where I had to use Index with 2 conditions the solution I found was to use the DGET function that allows you to look up for arrays as condition.

My morning was not quite good without this Google sheets tutorial. Thank you)

Quick note, you may use a single vlookup as a query (instead of copying and pasting in each row the same formula), by adding arrayformula at the beginning and using {.. } properties. 😊

This could not have shown up in my recommended at a better time. Thank you!

Excellent video! Love your makeover approach and seeing how you would handle these scenarios in both Google Sheets and Excel. Bravo!

That last one actually is gonna be really useful for me, for fixing one particular IF hell. Thank You !

@ExcelGoogleSheets

2 жыл бұрын

Awesome!

Always genius and neat way to solve problem

struggled understanding MAX, until you just explained it now.

Ok, I have used Google Sheets a lot, and I feel like I got 100% smarter after watching this video. Subscribed and will watch more of your stuff, thanks for this!

@ExcelGoogleSheets

2 жыл бұрын

Awesome! Thank you!

On the last part , what I would do is to create a handler and combine Region and Sales Rep with "&" then use that column as reference for the Vlookup Region&SalesRep Now I wonder which is less strenuous when it comes to processing .

@ExcelGoogleSheets

2 жыл бұрын

I've answered this many times in comments already and why it may not be necessarily a great idea.

@RazielBG

2 жыл бұрын

i would normally do a 2D table and combine VLOOKUP and HLOOKUP

@denismilic1878

2 жыл бұрын

you are right, in the lookup table just add one column when you combine Region and Sales with & or use QUERY function.

@ExcelGoogleSheets

2 жыл бұрын

@@RazielBG Great solution but not scalable to 3 or more column situations.

@0ExG0

2 жыл бұрын

For something like this you could use the DGET function, it allows you to look up for multiple condition as an array.

Thank you so much bro. This is a very needed lesson

@ExcelGoogleSheets

2 жыл бұрын

Glad to hear that

I already realized lookup tables were the way to go when I was first going through if function hell, but the use of max/min was something I hadn't thought of. Much easier to think about and doesn't require typing both values/functions twice each LMAO

15:00 forget the rest Just do another column =G1&H1 drag it down then do Lookup(Region&Sales...) done

sir, you have helped my life a lot. thank you

Great video, thanks for this.

@ExcelGoogleSheets

2 жыл бұрын

Glad you liked it!

Most of this is called data normalization, and it is standard practice for DBMS designers.

In my excel (2019), the MATCH function with 2 conditions (and the entire function as a result) didn't worked until I pressed Ctrl + Shift + Enter. I needed to use this lookup with 2 conditions some time ago and didn't knew how to do it, so thanks a lot!

Brilliant! Index/Match is powerful.

@ExcelGoogleSheets

2 жыл бұрын

:)

That was such a beautiful solution involving simple math and logic amazing

@ExcelGoogleSheets

2 жыл бұрын

Thank you

Another great tutorial again! Excellent verbal explanation. Though I still forget how to properly use functions like Index and Match, so I ended up using if/ifs.

@ExcelGoogleSheets

2 жыл бұрын

:)

Very useful! ♥

@ExcelGoogleSheets

2 жыл бұрын

Thank you

"Have you ever opened a formula and it looks like this?" Of course I know him. He's me

Another fabulous work and excellent explanation. Thanks, congratulations and please keep up the good work.

@ExcelGoogleSheets

2 жыл бұрын

Many thanks!

I've certainly been guilty of this in the past. Thanks to this channel I've grown way beyond nested if statement hell. Thanks LGS!

@ExcelGoogleSheets

2 жыл бұрын

Glad to hear

@michaelaustinealvarez7578

2 жыл бұрын

LGS!! LGS!! LGS!!

If you're only using Google Sheets you could also do the commision rate thing using the QUERY function, something like =QUERY("lookuptable!G:I", "select I where G= B1 AND H = C1") This is not the exact syntax, but it would be similar to this. QUERY() FTW!

Absolutely eyes opening! Thank you so much for the valuable advice! ❤️❤️❤️

@ExcelGoogleSheets

2 жыл бұрын

:)

Great educational video. Now if I can only figure out how and where to use these functions in my Google Sheets to make my workflow more efficient.

@ExcelGoogleSheets

2 жыл бұрын

:)

For the last example (i.e. the one simplifying the Commission Rate calculation), it would make much more sense to build a 2-way lookup table, which uses the two search criteria as its two axes; for instance, the Sales Representatives could be the rows of this 2-way table, whilst the Regions would be the columns... (Any entries left blank - i.e. no commission rate specified - would naturally be considered as "Not Applicable" for that particular combination of Sales Representative and Region...

@ExcelGoogleSheets

2 жыл бұрын

Agreed. As I've mentioned in the video there are many ways to do this, but I chose a solution to make sure it applies to many cases. For example, with my solution you can scale it to 3 or more conditions using the same logic. 2 way lookup is not scalable.

@johnangelico667

2 жыл бұрын

@@ExcelGoogleSheets If a commission calculation went to three conditions, I would recommend to the company that they simplify it. That is too complicated and employees would most likely be dis-incentivised as a result. I realise that is beyond the scope of this exercise but sorry, it needs to be said.

@johnangelico667

2 жыл бұрын

Agreed, and it could then be built together with the first employee table.

I feel so attacked right now 🙈 Ive definitely been “brute forcing” quite a few IF formulas. Thanks for this!!

11:25 Great Stuff!

@ExcelGoogleSheets

2 жыл бұрын

:)

Please include a warning when discussing array / control-shift-enter functions. The sumproduct function offers similar functionality without the risk of accidentally hitting the Enter key instead of CSE. Some will return an error message, others return an unintended value. In significant quantities, either option will impact performance.

@ExcelGoogleSheets

2 жыл бұрын

The problem with sumproduct is that it only works with numbers. In that cause use SUMIFS or better AVERAGEIFS

I clicked on this video to personally improve my function usage and ended up with a gem 💎 instead. First time knowing about N and the use case of INDEX MATCH this way 22:34 and now I want to experiment on some previous works. Thank you so much for sharing, always a pleasure to learn from your channel!

@drekmoorasdaw

2 жыл бұрын

Keep in mind though, that Index Match is volatile, meaning that it is recalculate every time Excel recalculates. Using it a few times is no problem, but using it in e.g. a table with a lot of rows, could be disastrous for your performance. Also, in newer versions of Excel you have XLookup, with you can often use in stead of Index Match.

@ExcelGoogleSheets

2 жыл бұрын

who said Index Match is volatile?

@ExcelGoogleSheets

2 жыл бұрын

you must be confusing it with OFFSET

@ExcelGoogleSheets

2 жыл бұрын

You can find the list of volatile functions here docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation

@drekmoorasdaw

2 жыл бұрын

@@ExcelGoogleSheets Apologies, my mistake. I still maintain however, that it is quite resource intensive, and should be avoided when working with large datasets, at least that's my experience, but other than that extremely useful.

for level column, you can use SWITCH function instead

You could also create a column (and then hide it or put it elsewhere) where you concatenate the sales rep and region into one - and then use index match to retrieve the commission rate.

@ExcelGoogleSheets

2 жыл бұрын

I prefer to not concatenate if possible, since it can produce undesired side effects & sometimes false positives.

@mikegervais2263

2 жыл бұрын

My own preference would have been to use sumproduct with the same logic to look for matches and return the commission rate. I wonder if there is a difference in performance for Sumproduct vs Index/Match?

@ExcelGoogleSheets

2 жыл бұрын

@@mikegervais2263 If you're going to use SUMPRODUCT then just use SUMIFS

@ExcelGoogleSheets

2 жыл бұрын

I don't think there will be much difference in performance.

I hate array formulas so much. But you are right, that is incredibly more complicated than the way I would do it. I would have done almost the same thing as you except I would have created a "helper column" or a "lookup column". I would have taken =G2&H2 filled the formula down then I would do a =Match(Concatenate(B2,C2),'Lookup Table'$I:$I,0) Create the table so it finds the Commission and done.. No array needed. That said... I use "look up tables" all the time, but I still have a "nested if" formula for a list of variables on one of my docs. it is basically going through a list of order lines and why those order lines may not have been shipped. Reasons can be: "No Inventory", "Order is already in process and must be shipped before a backorder can be processed", "The order is in Error due to incorrect location ID", "The order is in Error due to Duplicate Order". "Order already filled". "Order Not In System" There are 5 tables from various data bases that I have to bring together to get this information all 5 tables must match 3 columns to make sure we are getting the correct data, , , .. I will try your trick to see if it speeds up the Excel Workbook.

SUMPRODUCT solves every problem

Hahaha... I haven't even watched this, but feel like I attempted one

That was weirdly entertaining

Is it possible to do the commission rates with a 2d range? It makes more sense in my head for the data to be laid out with Reps going down a column and Regions across a row so you have a 2d array of rates.

@ExcelGoogleSheets

2 жыл бұрын

Yes. You can use INDEX with 2 MATCH functions.

Great video and formulas. Max instead of nested if. 💯👍

@ExcelGoogleSheets

Жыл бұрын

👍

While it's a cool logical trick, do *not* use multiplying boolean numbers as a substitute for logical AND. All that does is increase cognitive load and make your code harder to read. It also makes debugging harder since you're dealing with raw values rather than abstracts which can throw errors. Last thing you need is a silent glitch corrupting your data because of a mistake somewhere. Now, if you're *trying* to hurt the company (first of all, based) then *absolutely* do this. You'll be harder to replace and you'll be able to use that to get more bargaining power and higher wages. In fact, now that I think about it, it's kind of your moral duty as a worker to make your code as messy and impossible to work with as possible. Disregard everything: Do nested If.

@youssefhassan3072

2 жыл бұрын

I haven't tested it myself but I thought maybe he did this as using AND() would just automatically output one value which is equal to the anding of the all the values in both arrays and wont output a value for each row.

1:35 This is just data normalization and having additional lookup tables and keys. With excel structured referencing those things are tremendously simple and I would expect that anyone that actually uses excel as a tool knows all about this

@megaMagaManX8

2 жыл бұрын

You'd be surprised how many people are total trash with excel. It's rough

hello. thank you so much for your lessons! your mastery of explaining the principles is simply incredible! and the best thing is that you teach a variety of approaches to solving problems. I would like to ask a question: how to approach the solution of the problem when it is necessary to process customer orders with more than one sku per order? we have a family store and we process orders in sheets (we print address labels there). we have an sku-inventory in sheets and we import csv-orders there, then we use vlookup to process orders through inventory. but when there is more than one sku in the order, it is unclear what to do.

@ExcelGoogleSheets

2 жыл бұрын

This should help kzread.info/dash/bejne/kaV5xZp8dcq7nJc.html

@user-fq5vo9zs1q

2 жыл бұрын

@@ExcelGoogleSheets thank you !! supersql looks like magic. but this is the first time i did not understand how things from your lessons works :-)

You missed the default option in Office from the If of Main Office from the lookup, which you could have done with embedding the Vlookup in an iferro statement, if for some reason they didn't want to list everyone. Though then technically you could also remove all the people in the lookup who were based in Main Office.

does google dpreadsheet does not convert true / false automatically to 1/0 when multiplied? in excel multiplying trues and false returns 1 or 0.

i dont use sheets very much, but ive found it useful with something im doing. the last example was super informative, but i was wondering what you would do if you had 3 or more things you needed to match up. maybe this isnt even what i need an example of what im trying to do is figuring out combinations. if i reached into a bag filled with different objects, theres a 1/100 chance of it being a certain shape, color and material. if its a red, paper cube then its what i want but if its a blue paper cube, red metal cube or a red paper pyramid then its wrong. would there be a way to write it, even if its just a yes or no statement? id much prefer if it was able to say how many things matched if they didnt, so a blue metal pyramid would be no but a blue paper cube would be 2, but im perfectly happy with just a yes or no statement the main problem is, i cant just brute force it with a bunch of if statements, because my real example has over 1000 combinations for each bag, and over 100 bags, with each bag having its own "perfect" or ideal object. even if i cant get a perfect formula, i want one thats at least manageable

When I have to columns to do an AND and lookup, I like to simply create a key, concatenating both... than a simple procv would work to bring the comission.

@TheRedsMan11

2 жыл бұрын

This was my thought too. Matching on concatenated values seems a bit more straightforward.

@ExcelGoogleSheets

2 жыл бұрын

I've already answered this about 50 times, but concatenating like this can return false positives. Col1 Col2 Col3 Yes 55 Yes 33 If you concatenate column 1 & 2 they'll both seems like the same value "Yes", but in reality it's not the same thing.

hey i was searching for a method for auto change letter type and size for example if i’m sharing the sheet with others and they inputs different types of letter and different size it will be automatically adjusted to what i want is there a way to do that?

Hi.. could you assist me on below issue? require data from one sheet to another. If a1 is blank then pick down cell which is not blank.. e.g. if a1 to a3 is blank and a4 have some values then pick the a4 values under a1 cell in another sheet...

I was feeling really confident in my Excel skills until that last example. Wow.

Thanks for the thorough explanation! I was already familiar with these techniques, but I will point my colleagues to this video instead of explaining it myself :) Just one thing, I don't use the N function since a Boolean is already treated as 0/1 in a multiplication. Did you find cases where this is not valid?

@ExcelGoogleSheets

2 жыл бұрын

There are a few scenarios where N is necessary, largely should be OK to not use it.

GOAT!!!

@ExcelGoogleSheets

2 жыл бұрын

:)

Thank your for this excellent video. I really appreciate the way you explain how it works and shows each step before merging them into one formula. I recently saw someone using the filter formula. I have one query for which I need the formula to look up and display information from app 30,000 rows by 8 columns and I look up up to 80 separate data points for each user. Is there a way of knowing which formula uses the least computing resources.

@ExcelGoogleSheets

2 жыл бұрын

Not really. This video will show a technique that may help kzread.info/dash/bejne/eJ94lNaQdK_Th6g.html

I've used IF statements and knew to avoid hard coding because IFs are tricky enough

I didn’t even know that If statements were a thing in excel… I was only taught to use lookup functions…

Teacher...My Brain Hurts!

@ExcelGoogleSheets

2 жыл бұрын

:)

Far too complicated a solution for the commission rate. Just concatenate the two variables in the lookup.

How did you drag the formula in 8:16?

What if we put a new entry in the sheet? will it automatically generate the result or we need to drag the fill handle again & again. How can we automate the process..?

I'm just 3 1/2 minutes into the video and I already feel like I should Venmo this guy like a thousand bucks just to pay him for all the effing time he's gonna save me with this stuff!!!

You can't always add more sheets, and sometimes they are just unnecessary... In that first case I would just go with switch function, and maybe add more whitespace the formula as well.

The very first issue with the nested if statements, it doesn’t feel like a real solution, because the previous if statements were based on the region column. Now if you never had the if statements in the first place, you couldn’t have done it just like that. I wish you had shown a solution that could’ve been used in the from the get-to before ever making the if statements.

I always learn something new or better in your videos. Thanks! But two things: 1. That last simpler function is much more advanced than the previous. 2. Could the last formula use the same vlookup strategy but having salesRep consternated with region add key?

@ExcelGoogleSheets

2 жыл бұрын

I prefer to not concatenate if possible since it can produce undesired side effects & sometimes false positives. If you want a simple solution, just use SUMIFS or AVERAGEIFS. Given that there will be only one match, it should work just fine.

@ExcelGoogleSheets

2 жыл бұрын

This video can give you some idea why I try to stay away from combining text values kzread.info/dash/bejne/amV5sbyvZb2TmdI.html

do you have a discord? or a way to talk live? looking to do something on sheets and I'm a beginner and can't find a tutorial.

my favorite is using *MAX()* instead of *IF*

Can you tell us how to get these working again running under Windows XP ?

If the person doesn't use "IFS" instead of IF, you know the person isn't a experienced at all.

@ExcelGoogleSheets

2 жыл бұрын

It's either that or it's a 10 year old spreadsheet.

@PJAnand

2 жыл бұрын

It is really funny to think about the irony in this comment. If you are truly experienced, you will know that 'if' has its place and 'ifs' has its place. Don't act all mighty once you learn a new gimmick. Let's see. I want to check if employee name is David or not. If David I need to output owner or else employee. Now if you write this statement will 'ifs' to show your experience, I will definitely know that you are not good with your basics. Please don't forget fundamentals. Everything has a purpose. Thank you.

My sheets are always a hell of IFs + 😅 Im still in the learning process, but personaly I feel more safe in the IF zone, as it throws out less errors. Whenever iv tried query or lookup, somethin seems to end up wrong and I cant understand how to fix it lol.

@ExcelGoogleSheets

2 жыл бұрын

Sounds like you need to spend a little time to learn more about lookups.

Awesome video! Quick question for the commission column. Would using =CONCATENATE to create a unique ID (ex:officename) on a new column and then use VLOOKUP be feasible? Any limits or potential of it breaking? Thanks!

@szachu1996

2 жыл бұрын

I would also use another column with CONCATENATE formula and VLOOKUP because its looks simpler. And it can break only if you add data and wont drag down CONCATENATE formula.

@fisknitrox

2 жыл бұрын

Would do the same. Would fail in same way as over complicated array formulas

The last case is similar to boolean indexing in Python, in that case you could instead of multiplying the boolean arrays you just use the "and" keyword which is actually the logic you're doing. I wonder why that won't work here.

i'm pretty sure the last formula could be even more optimized with =Index and Equiv

@ExcelGoogleSheets

2 жыл бұрын

Equiv is the same as Match, no? I believe it's translated for some reason.

genio

Use vlookup array to use not dragdrop formula is possible?

@ExcelGoogleSheets

2 жыл бұрын

Sure. You can watch array formula videos for examples.

4:48 it over 9000

Please Provide some tutorial about How to Fetch MySQL Data on Google Sheet

How can I hire you to help me?

As someone who does programming for fun the first few minutes were torture

Me who literally just did some horrible IF functions in Google Sheets... Google is watching me o.o

So basically the if statement hell is taking a nice easy to look at table and through sheer ignorance turn it into a wall of text

Cool

@ExcelGoogleSheets

2 жыл бұрын

:)

I love nested ifs. Sometimes ifs and vlookup or sumifs inside as mu conditions

Need a help. I'm new to Google data studio, found it via your channel. I tried to connect it to my Google sheet. However, it displays invalid dimension. To give a context, my google sheet is styled. Like, it has various designs. How can I reconcile the data? The google data studios seemed to not find the data I want to be looked on the sheets. :'(

@ExcelGoogleSheets

2 жыл бұрын

When you set up a data source and specify to use Google Sheets, there is an option box to specify which range to use from the spreadsheet.

Create multiple tabs and name tabs according to the value in each cell from range on Google sheet, what is the way to do it?

@ExcelGoogleSheets

2 жыл бұрын

Script

@anhnhatnguyet4628

2 жыл бұрын

@@ExcelGoogleSheets thank you. Can you make a video tutorial on this?

why overcomplicate this 21:58 ? since the result is a number, sumifs would work perfectly, or better yet normal sum function with easy arrey comparison. alternatively you can do VL with choose combo or if combo to simplify the formula

@ExcelGoogleSheets

2 жыл бұрын

OK, so the issue with SUMIFS or other SUM is that 1. it returns a zero when there is no match 2. if there are accidental duplicates it will double the number. So I suggest use AVERAGEIFS instead if you feel INDEX/MATCH solution is complicated.

Is there a Google sheets formula to count the number of filtered rows?

@ExcelGoogleSheets

2 жыл бұрын

SUBTOTAL function should help you.

Why would you not just do a query to do the lookup in the last one. That was such a complex way at the end. Query (lookup range, select Col3 where col1 = b2 and Col2 = c2 limit1) Little formatting needed for it to work but much more simple

@ExcelGoogleSheets

2 жыл бұрын

Not a big fan of using QUERY function for each row. In my experience things really slow down with it.

Why not use ifs? Thx

Yes sir you are right, but I feel adding lot of helper sheets will increase the limit of 500000 cells unnecessarily and if formulas can be wrapped inside another to get objective , no harm, need to have eagle eye and patience to check with f9 for result.

@peterkalu5392

2 жыл бұрын

Multiple nested formulas can impact sheets performance by slowing it down; also very difficult to manage. Helper sheets is really a better solution.

@a13ph0

2 жыл бұрын

You can store this lookup data inside literal array instead, right in the formula cell. It's still more readable than nested ifs

@ExcelGoogleSheets

2 жыл бұрын

If you have issues fitting your data within 5 mil cell limit, then I believe you'll have much more serous problems in your spreadsheet than these little helper tables.

i am struggling with a function right now and i wonder if you could help: i would like cell A1 to say "yes" IF all the cells from A2:A10 also say "yes". if any of the cells A2:A10 say "no" then A1 should also say "no". but unfortunately the IF function only takes A2="yes", not A2:A10="yes"... there must be a way to do this, i just cannot figure it out. maybe you/someone has a hint for me here :) appreciate the help

@ExcelGoogleSheets

2 жыл бұрын

=IF(COUNTIFS(A2:A10,"yes")=ROWS(A2:A10),"yes","no")

Re: the formula around 18:00 sec. I think it's easier to to it with DGET. no? DGET is perfect for 2 column criteria.

@ExcelGoogleSheets

2 жыл бұрын

Would work fine in Google Sheets, not in Excel. The thing I don't like about DGET is that you have to concatenate "=" in front of text values in order to get accurate results. If I'm going to do that then I'd rather do INDEX/MATCH.

@OmriLevy

2 жыл бұрын

@@ExcelGoogleSheets my suggestion would be using the following formula in J2: =DGET(lookuptable,"commision",{"Region","sales rep";B2,C2}) what do you think?

@ExcelGoogleSheets

2 жыл бұрын

@@OmriLevy Wouldn't work. You have to do =DGET(lookuptable,"commision",{"Region","sales rep";"="&B2,"="&C2})

@ExcelGoogleSheets

2 жыл бұрын

Watch my database functions video to understand why.

@OmriLevy

2 жыл бұрын

@@ExcelGoogleSheets very strange. I use it without the = sign every day, learnt most of what I know in Google Sheets from you (and from Ben Collins) Anyway, I'll watch your video again.

=ARRAYFORMULA(VLOOKUP('Lookup Table'!B2:B & 'Lookup Table'!C2:C, { 'Lookup Table'!G2:G &'Lookup Table'!H2:H, 'Lookup Table'!I2:I }, 2, 0 )) This should get the same result right?

@ExcelGoogleSheets

2 жыл бұрын

If you decide to concatenate, at least use a separator, otherwise the likelihood of false positives is high.

Hallo sir I have a question please help me 1) i create a Google sheets and I want Mack a copy in a folder that Google sheets in Excel format 2) i want send that converted excel file to a particular email IDs All this process I want to do automatically How will I do please help me 🙏. If possible please my video on it Thanks a lot

So this is for people who don't know how to use SAS, R, or Python to wrangle data in a superior fashion.

I avoid complicated nested IF statements whenever possible.