IF Functions from Hell in Excel & Google Sheets Formulas

[@blakepeterson1977]

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.

[@johanlarsson9805]

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]

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]

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]

@@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!

[@so_fia]

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

[@jodhvirsingh8677]

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]

Thanks!

[@thehawk40375]

@@ExcelGoogleSheets please share practice file for this video kzbin.info/www/bejne/gJiQl5Zuj7ejfMU

[@davidvansickle9545]

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]

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

[@mattshu]

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

[@tuxbuttocks8973]

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

[@stevenwilson5556]

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]

Awesome! Thank you!

[@lloydmcleod629]

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

[@MISComputerNET]

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

[@ExcelGoogleSheets]

Many thanks!

[@biondatiziana]

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

[@bradleyhellendoorn5125]

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

[@locksbyjerocelle1101]

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]

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]

who said Index Match is volatile?

[@ExcelGoogleSheets]

you must be confusing it with OFFSET

[@ExcelGoogleSheets]

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

[@drekmoorasdaw]

@@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.

[@massimosabbatini5570]

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. 😊

[@conradnebeker]

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

[@ExcelGoogleSheets]

:)

[@oscarlee1693]

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

[@ExcelGoogleSheets]

Thank you

[@0ExG0]

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.

[@petzkhie]

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]

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

[@RazielBG]

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

[@denismilic1878]

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

[@ExcelGoogleSheets]

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

[@0ExG0]

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

[@Dennson1]

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

[@kimaji]

struggled understanding MAX, until you just explained it now.

[@memory_null]

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]

Glad to hear

[@michaelaustinealvarez7578]

LGS!! LGS!! LGS!!

[@EJP286CRSKW]

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

[@an0therNet]

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

[@ExcelGoogleSheets]

Awesome!

[@minhthai2804]

Always genius and neat way to solve problem

[@anthonyescribens]

Brilliant! Index/Match is powerful.

[@ExcelGoogleSheets]

:)

[@DavidDavidson-tristar500]

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!

[@nicadi2005]

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]

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]

@@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]

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

[@JohnJillky]

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

[@michaelaustinealvarez7578]

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]

:)

[@ziksy6460]

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

[@neogamma2128]

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

[@danaewilson924]

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

[@Hermaniac8]

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]

Yes. You can use INDEX with 2 MATCH functions.

[@blakepeterson1977]

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]

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

[@eoln_]

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!

[@user-cg5fh3bi5j]

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

[@ExcelGoogleSheets]

Glad to hear that

[@Nein1no]

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]

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

[@mikegervais2263]

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]

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

[@ExcelGoogleSheets]

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

[@rjaquaponics9266]

11:25 Great Stuff!

[@ExcelGoogleSheets]

:)

[@rrrraaaacccc80]

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

[@ExcelGoogleSheets]

👍

[@omegahaxors3306]

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]

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.

[@tallUNLIMITED]

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

[@lpanebr]

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]

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]

This video can give you some idea why I try to stay away from combining text values kzbin.info/www/bejne/bmKogI2sar9gn9E

[@TheBearDenPhotography]

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]

:)

[@mans0011]

Great video, thanks for this.

[@ExcelGoogleSheets]

Glad you liked it!

[@th3n3wk1dd]

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.

[@TheoriginalBMT]

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

[@congtranxuan6551]

for level column, you can use SWITCH function instead

[@Asparuh.Emilov]

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

[@ExcelGoogleSheets]

:)

[@ImEbo]

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?

[@lydierayn]

That was weirdly entertaining

[@drz1]

SUMPRODUCT solves every problem

[@fyrman9092]

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

[@johanlarsson9805]

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]

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

[@JakeEpooh]

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!!!

[@MKRM27]

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

[@user-fq5vo9zs1q]

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]

This should help kzbin.info/www/bejne/laKolGt5esyIopY

[@user-fq5vo9zs1q]

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

[@andywai1567]

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]

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]

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

[@JNC901]

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.

[@alliillilliii4365]

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

[@ExcelGoogleSheets]

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

[@PJAnand]

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.

[@mr8ball1st]

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.

[@smanzoli]

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]

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

[@ExcelGoogleSheets]

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.

[@ElZamo92]

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

[@tobywood9156]

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

[@Puner54]

Teacher...My Brain Hurts!

[@ExcelGoogleSheets]

:)

[@AndreaGhensi]

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]

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

[@mirag3304]

When I see things like this, I always feel like it should be done in a relational database like Access instead of in Excel. Unfortunately, most people seem to be really intimidated by Access while Excel can largely be understood by looking at it (in its most basic uses). I will say that this kind of design in Excel is less overhead to get going and get output from in the beginning.

[@byronstutorialspage2134]

I'd just build my relational database in Excel.... using Power Query and Data Modelling

[@FSANCHEZog]

GOAT!!!

[@ExcelGoogleSheets]

:)

[@amartyasarkar4505]

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..?

[@muhammadarsyad2021]

=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]

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

[@TommFire]

Very useful! ♥

[@ExcelGoogleSheets]

Thank you

[@MetsuNoShin]

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.

[@zummon]

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

[@shawnchristophermalig4339]

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]

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.

[@millomweb]

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

[@ExcelInstructor]

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]

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.

[@_WFHHub]

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...

[@vampy5071]

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]

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

[@ExcelInstructor]

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

[@evann4391]

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

[@ExcelGoogleSheets]

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

[@ceaceelya]

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]

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

[@12chachachannel]

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.

[@ShriRam-ip1wr]

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

[@adityadeepakghadge5738]

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

[@christianvestergaard9906]

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]

Not really. This video will show a technique that may help kzbin.info/www/bejne/fJynY6eNebGgjac

[@t2udu]

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.

[@echoes6092]

Another quick note-you can multiply boolean values without converting them to numbers first (which was using the N() function)! E.g. if A1 is TRUE and A2 is FALSE, and A3 = A1 × A2, then A3 = 0. You can also do any other arithmetic operation.

[@RobAshwell]

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]

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

[@Freak80MC]

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

[@4lliii]

How did you drag the formula in 8:16?

[@fatecreatr]

For the commission rate, why not just do an arrayformula vlookup on a joined nested table? Also, you can name ranges in Sheets as well. [ Data!J2 =arrayformula(if(B2:B=“”,””,vlookup(B2:B&C2:C,{‘Lookup Table’!G:G&’Lookup Table’!H:H,’Lookup Table’!I:I},2,false))) ]

[@ExcelGoogleSheets]

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.

[@fatecreatr]

@@ExcelGoogleSheets I've tested and used this and it does work. "Yes" and a blank on either side won't come up because Col1 of the lookup table is a concatenation of G and H and Col2 from I is the commission rate. You could also go the long way around and join the G and H columns in the lookup table as a separate formula (this can make diagnostics easier) and do a vlookup against that if you want, but it does work. I'm not using match to validate the data, it's a straight lookup. If your lookup table was more generic, with actual "Yes" and blank spaces, then yeah, the lookup on a concatenation won't work, but this is not that table. I don't know off hand which of the three methods is less computationally, but I do know that my formula above is a single formula to do the whole column. I'm simply suggesting an alternate process.

[@craidiefin]

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

[@jzburda]

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

[@pydiboy]

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

[@dinner4417]

Hi! I posted into your fuzzymatch video from 2 years ago, and I just wanted to know if you still read those comments.

[@ExcelGoogleSheets]

Yes, I do if I have time. It looked you you edited your original comment and you resolved the problem.

[@sumitmukherjee3818]

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]

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

[@a13ph0]

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

[@ExcelGoogleSheets]

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.

[@YanRakhmanov]

How can I hire you to help me?

[@hendragunawan3593]

Why not use ifs? Thx

[@anhnhatnguyet4628]

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]

Script

[@anhnhatnguyet4628]

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

[@ManongChito]

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

[@willowbrook8917]

I did my array formula just like yours and when I hit enter I get this. "Error Result was not automatically expanded, please insert more rows (2)". Why?

[@ExcelGoogleSheets]

Not exactly sure what you did, but generally that means there are not enough rows in your sheet for array formula results. Adding more rows may fix it.

[@willowbrook8917]

​@@ExcelGoogleSheets It was a new blank sheet that I had just opened. I just add 2 more rows and it worked fine. Thanks.

[@willowbrook8917]

@@ExcelGoogleSheets I see a problem that could happen. I moved the column in the "Lookup table" and the array formula did not change the location. So the array formula was still looking for the data in the original column.

[@willowbrook8917]

I don't know why, but this is not working for me. I have my formula exactly like yours but the "True" does not show up in the correct rows. In fact, I am getting a "True" in a blank row. I am just trying to learn what you are teaching. I have letters in column "C" and numbers in column "D" in two different sheets named "Lookup Table' and "Data". I am using 16 rows. I have letters A, B, C, and D repeated 4 times in "C" column and numbers 1, 2, 3, and 4 repeated 4 times in "D" column. I have a formula, =ArrayFormula(C3='Lookup Table'!C:C

[@douglasteixeiradeabreu]

Use vlookup array to use not dragdrop formula is possible?

[@ExcelGoogleSheets]

Sure. You can watch array formula videos for examples.

[@prempatel1501]

How can i Get time and date in column B when Data in Column A gets a change? Please Help me with this?

[@ExcelGoogleSheets]

kzbin.info/www/bejne/a2WblXdpnr2Yqsk

[@prempatel1501]

@@ExcelGoogleSheets Thanks for your amazing videos...