Комментарии •

  • @blakepeterson1977
    @blakepeterson1977 2 года назад +19

    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
    @johanlarsson9805 2 года назад +11

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

  • @jodhvirsingh8677
    @jodhvirsingh8677 2 года назад +30

    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
      @ExcelGoogleSheets 2 года назад +1

      Thanks!

    • @thehawk40375
      @thehawk40375 2 года назад

      @@ExcelGoogleSheets please share practice file for this video
      ruclips.net/video/Jg-fc8ZRpFc/видео.html

    • @davidvansickle9545
      @davidvansickle9545 2 года назад +6

      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
      @brightmatter 2 года назад

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

    • @mattshu
      @mattshu 2 года назад

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

  • @0ExG0
    @0ExG0 2 года назад +1

    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.

  • @so_fia
    @so_fia 2 года назад

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

  • @stevenwilson5556
    @stevenwilson5556 2 года назад

    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!

  • @bradleyhellendoorn5125
    @bradleyhellendoorn5125 2 года назад

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

  • @anthonyescribens
    @anthonyescribens 2 года назад +1

    Brilliant! Index/Match is powerful.

  • @eoln_
    @eoln_ 2 года назад

    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!

  • @minhthai2804
    @minhthai2804 2 года назад +1

    Always genius and neat way to solve problem

  • @petzkhie
    @petzkhie 2 года назад +14

    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
      @ExcelGoogleSheets 2 года назад +1

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

    • @RazielBG
      @RazielBG 2 года назад +2

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

    • @denismilic1878
      @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
      @ExcelGoogleSheets 2 года назад +2

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

    • @0ExG0
      @0ExG0 2 года назад

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

  • @conradnebeker
    @conradnebeker 2 года назад

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

  • @oscarlee1693
    @oscarlee1693 2 года назад +15

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

  • @biondatiziana
    @biondatiziana 2 года назад +3

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

  • @locksbyjerocelle1101
    @locksbyjerocelle1101 2 года назад +4

    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
      @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
      @ExcelGoogleSheets 2 года назад

      who said Index Match is volatile?

    • @ExcelGoogleSheets
      @ExcelGoogleSheets 2 года назад

      you must be confusing it with OFFSET

    • @ExcelGoogleSheets
      @ExcelGoogleSheets 2 года назад +1

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

    • @drekmoorasdaw
      @drekmoorasdaw 2 года назад +1

      @@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
    @massimosabbatini5570 2 года назад +14

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

  • @an0therNet
    @an0therNet 2 года назад

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

  • @tuxbuttocks8973
    @tuxbuttocks8973 2 года назад

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

  • @MISComputerNET
    @MISComputerNET 2 года назад +6

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

  • @neogamma2128
    @neogamma2128 2 года назад

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

  • @nicadi2005
    @nicadi2005 2 года назад +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
      @ExcelGoogleSheets 2 года назад +1

      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
      @johnangelico667 2 года назад +1

      @@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
      @johnangelico667 2 года назад

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

  • @DavidDavidson-tristar500
    @DavidDavidson-tristar500 2 года назад +2

    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!

  • @michaelaustinealvarez7578
    @michaelaustinealvarez7578 2 года назад +4

    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.

  • @kimaji
    @kimaji 2 года назад

    struggled understanding MAX, until you just explained it now.

  • @СэржпүрэвМөнх-Эрдэнэ

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

  • @memory_null
    @memory_null 2 года назад +4

    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!

  • @Dennson1
    @Dennson1 2 года назад +2

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

  • @JohnJillky
    @JohnJillky 2 года назад

    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

  • @lloydmcleod629
    @lloydmcleod629 2 года назад +1

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

  • @EJP286CRSKW
    @EJP286CRSKW 2 года назад +11

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

  • @blakepeterson1977
    @blakepeterson1977 2 года назад +7

    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
      @ExcelGoogleSheets 2 года назад +2

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

  • @rjaquaponics9266
    @rjaquaponics9266 2 года назад +1

    11:25 Great Stuff!

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

  • @rrrraaaacccc80
    @rrrraaaacccc80 2 года назад

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

  • @Nein1no
    @Nein1no 2 года назад +6

    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
      @ExcelGoogleSheets 2 года назад

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

    • @mikegervais2263
      @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
      @ExcelGoogleSheets 2 года назад +1

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

    • @ExcelGoogleSheets
      @ExcelGoogleSheets 2 года назад

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

  • @th3n3wk1dd
    @th3n3wk1dd 2 года назад

    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.

  • @mans0011
    @mans0011 2 года назад +1

    Great video, thanks for this.

  • @congtranxuan6551
    @congtranxuan6551 2 года назад +1

    for level column, you can use SWITCH function instead

  • @danaewilson924
    @danaewilson924 2 года назад

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

  • @ziksy6460
    @ziksy6460 2 года назад

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

  • @omegahaxors9-11
    @omegahaxors9-11 2 года назад +2

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

  • @tallUNLIMITED
    @tallUNLIMITED 2 года назад

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

  • @Asparuh.Emilov
    @Asparuh.Emilov 2 года назад +1

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

  • @Hermaniac8
    @Hermaniac8 2 года назад +3

    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
      @ExcelGoogleSheets 2 года назад +1

      Yes. You can use INDEX with 2 MATCH functions.

  • @drz1
    @drz1 2 года назад

    SUMPRODUCT solves every problem

  • @johanlarsson9805
    @johanlarsson9805 2 года назад

    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
      @megaMagaManX8 2 года назад

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

  • @fyrman9092
    @fyrman9092 2 года назад +1

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

  • @TheoriginalBMT
    @TheoriginalBMT 2 года назад

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

  • @12chachachannel
    @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.

  • @mr8ball1st
    @mr8ball1st 2 года назад

    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.

  • @lydierayn
    @lydierayn 2 года назад

    That was weirdly entertaining

  • @echoes6092
    @echoes6092 2 года назад +1

    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.

  • @JakeEpooh
    @JakeEpooh 2 года назад

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

  • @smanzoli
    @smanzoli 2 года назад

    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
      @TheRedsMan11 2 года назад

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

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

  • @MetsuNoShin
    @MetsuNoShin 2 года назад

    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.

  • @lpanebr
    @lpanebr 2 года назад +3

    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
      @ExcelGoogleSheets 2 года назад +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
      @ExcelGoogleSheets 2 года назад +2

      This video can give you some idea why I try to stay away from combining text values ruclips.net/video/81EOZv5Z-io/видео.html

  • @ShriRam-ip1wr
    @ShriRam-ip1wr 2 года назад

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

  • @zummon
    @zummon 2 года назад +1

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

  • @TommFire
    @TommFire 2 года назад

    Very useful! ♥

  • @mirag3304
    @mirag3304 2 года назад +2

    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.

    • @tutorials--1234
      @tutorials--1234 2 года назад

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

  • @ДмитрийПаравин
    @ДмитрийПаравин 2 года назад

    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
      @ExcelGoogleSheets 2 года назад

      This should help ruclips.net/video/_qEc8CEgUl4/видео.html

    • @ДмитрийПаравин
      @ДмитрийПаравин 2 года назад

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

  • @evann4391
    @evann4391 2 года назад

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

    • @ExcelGoogleSheets
      @ExcelGoogleSheets 2 года назад

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

  • @ImEbo
    @ImEbo 2 года назад +1

    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?

  • @FSANCHEZog
    @FSANCHEZog 2 года назад +1

    GOAT!!!

  • @ElZamo92
    @ElZamo92 2 года назад

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

  • @vampy5071
    @vampy5071 2 года назад

    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
      @ExcelGoogleSheets 2 года назад +2

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

  • @MKRM27
    @MKRM27 2 года назад +2

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

  • @amartyasarkar4505
    @amartyasarkar4505 2 года назад

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

  • @Puner54
    @Puner54 2 года назад +1

    Teacher...My Brain Hurts!

  • @millomweb
    @millomweb 2 года назад

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

  • @JNC901
    @JNC901 2 года назад

    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.

  • @RobAshwell
    @RobAshwell 2 года назад

    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
      @ExcelGoogleSheets 2 года назад

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

  • @muhammadarsyad2021
    @muhammadarsyad2021 2 года назад

    =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
      @ExcelGoogleSheets 2 года назад +1

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

  • @sumitmukherjee3818
    @sumitmukherjee3818 2 года назад

    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
      @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
      @a13ph0 2 года назад +1

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

    • @ExcelGoogleSheets
      @ExcelGoogleSheets 2 года назад +3

      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.

  • @fatecreatr
    @fatecreatr 2 года назад

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

    • @fatecreatr
      @fatecreatr 2 года назад +1

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

  • @AndreaGhensi
    @AndreaGhensi 2 года назад

    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
      @ExcelGoogleSheets 2 года назад +1

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

  • @t2udu
    @t2udu 2 года назад

    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.

  • @tobywood9156
    @tobywood9156 2 года назад

    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

  • @4lliii
    @4lliii 2 года назад

    How did you drag the formula in 8:16?

  • @ExcelInstructor
    @ExcelInstructor 2 года назад +1

    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
      @ExcelGoogleSheets 2 года назад +1

      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.

  • @alliillilliii4365
    @alliillilliii4365 2 года назад +2

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

    • @ExcelGoogleSheets
      @ExcelGoogleSheets 2 года назад +2

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

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

  • @hendragunawan3593
    @hendragunawan3593 2 года назад

    Why not use ifs? Thx

  • @keysandquestions
    @keysandquestions 2 года назад

    How can I hire you to help me?

  • @ceaceelya
    @ceaceelya 2 года назад

    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
      @ExcelGoogleSheets 2 года назад +1

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

  • @adityadeepakghadge5738
    @adityadeepakghadge5738 2 года назад

    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

  • @ManongChito
    @ManongChito 2 года назад +1

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

  • @dinner4417
    @dinner4417 2 года назад

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

    • @ExcelGoogleSheets
      @ExcelGoogleSheets 2 года назад

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

  • @Freak80MC
    @Freak80MC 2 года назад

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

  • @pydiboy
    @pydiboy 2 года назад

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

  • @ExcelInstructor
    @ExcelInstructor 2 года назад

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

  • @shawnchristophermalig4339
    @shawnchristophermalig4339 2 года назад

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

  • @pksublime
    @pksublime 2 года назад

    Isn't N redundant? Multiply will implicitly convert for you.

  • @wickedbird1538
    @wickedbird1538 2 года назад

    I avoid complicated nested IF statements whenever possible.

  • @12chachachannel
    @12chachachannel Год назад

    a better vlookup can be done with VLOOKUP(s1col1&"some delimiter not present in the lookup table"&s1col2, s2col1&"same delimiter"&s2col2,2,0)

    • @ExcelGoogleSheets
      @ExcelGoogleSheets Год назад

      You can also do this ruclips.net/video/q4kYG1RbTic/видео.html

  • @_WFHHub
    @_WFHHub 2 года назад

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

  • @anhnhatnguyet4628
    @anhnhatnguyet4628 2 года назад

    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?

  • @jzburda
    @jzburda 2 года назад

    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

  • @maxquayle2519
    @maxquayle2519 2 года назад

    4:48 it over 9000

  • @JeanYvesHemlin
    @JeanYvesHemlin 2 года назад

    Wouldn't it be easier to use SUMPRODUCT instead?

    • @ExcelGoogleSheets
      @ExcelGoogleSheets 2 года назад

      It would only work with number values & it's potentially dangerous if you have accidental duplicates.

  • @douglasteixeiradeabreu
    @douglasteixeiradeabreu 2 года назад

    Use vlookup array to use not dragdrop formula is possible?

    • @ExcelGoogleSheets
      @ExcelGoogleSheets 2 года назад

      Sure. You can watch array formula videos for examples.