The key here is non-exact matching in the VLOOKUP. Notice she didn’t include the last argument so it defaulted to true. This allows non-exact matching which assumes the lookup column is sorted in ascending order and matches the first row where the lookup value is greater.

@galvem01

2 жыл бұрын

Thanks for explaining it!

@menggay8215

2 жыл бұрын

Very helpful

@BulshoSomali

2 жыл бұрын

Agree I can't see FALSE OR 0 ( in accounting you need no be 100% true )

@Griffiths1

Жыл бұрын

I thought the default was false for this? Or am I mistaken, I’ve never not written it actually, just thinking about it.

@mariaw7746

Жыл бұрын

Using it

I appreciate these small examples but that 55 buggling me hard. ;)

@noctusowl

2 жыл бұрын

Some sort of multiple choice that would increase the oddsof scoring something even if you don't know nothing. Just did a test with 67% pass grade because of this.

@eduardosanchez2558

Жыл бұрын

Totally

OMG all my life I have been using IF functions, which takes a while to set. Wish I knew this method from before. Leila you are a life saver!!!!

There is an error in your bins. Both F and D contain value of 55

@Pranit.Gaikwad

Жыл бұрын

Yes typo error, but hope understood the scenario

Please send Sid Sloth to my office. That grade is outrageous.

@Bejunckt

Жыл бұрын

He only got his name right on his test

Student: Teacher, why did you gave me an F? Teacher: I was just following Leila’s Excel tips.

I agree that one should avoid doing tasks manually whenever possible. Just a note on your method; for those that do not want the extra columns containing the grading criteria to show, hiding columns will not impact the function. Alternatively, this can be accomplished with a nested IF statement. =IF(I2>=0.905,"A",IF(I2>=0.805,"B",IF(I2>=0.705,"C",IF(I2>=0.605,"D",IF(I2

I really appreciate this. Learned this on my own. Thank you for explaining it.

Very helpful...thanks for sparing time to make this video

She's amazing

Mam you earned me as subscriber beautifully explained

I live this tip!

Excel with automation! Magic! Thanks Leila!🙂

You could also try with switch, ifs and nested if

Good to know! Thanks a lot!

Thank You. Learned 03 new things. F4, flash fill and the task itself

Of course Sheldon Cooper has A with score 99 Thanks for nice tip, it is great as always

God bless you Leila!

This is is so easy if with if formula

These videos make me so excited

Christopher Moltisanti 👌🏼👌🏼

@LeilaGharani

2 жыл бұрын

Loved the show!

Great demo of xlooup thanks

Thanks for the how to. It almost feels like you need to be a programmer to work excel.

Excellent Tip...Thank you

Advance ms office excel, you’ll learn more.. Appreciate this one too👍

I’m so glad to be subscribed to your channel.

Superb👍 thank you very much this video❤

Intervals: Text to Columns, and people should know the last (missing here) parameter of VLOOKUP function, which requires the proper ordering of the data and tells what value will be used in the case of an inaccurate match

This trick was very useful instead of if function

Thanks a lot for this tips

It works because she's using the Approximation option of the VLOOKUP formula which will assign a value if the number is higher than the lower bound of a category. Works only on numbers and only if the score is ordered from lowest to highest.

@LeilaGharani

2 жыл бұрын

True. If it's not ordered from lowest to highest, you can use XLOOKUP.

This is awesome,thanks!

THANK YOU! Your tutorial just helped me solved a huge problem.

@LeilaGharani

Жыл бұрын

Glad it helped!

Thanks for sharing!

Very informative thank you

This is awesome!

Thanks for the tip

Same with lookup let take colum of letters more freely

Wow .. i didn't know ..🤯 will try .i am seriously loving this channel's youtube shorts ... so much to learn 💟

Wow!!! I used 'if' function for this, which is very confusing. This video shows a way more simple formula.

I follow your Every content. Also I will try. Thanks dear from Bangladesh.

I love you and the way teach

You got a sub from me before. But this vid is gold!

am also suprised you didnt use tables here rather than fixed refs, but love your stuff

Great use of vlookup

Nice presentation

I needed this

Wait... Doesn't vlookup look for exact match? How does vlookup work if the value isn't in column 1??

@sleepywhentired6364

2 жыл бұрын

Exactly! I am very curious to know how this works. It looks very useful but how does the function determine if I want lookup within a range or exact values?

@imserge9559

2 жыл бұрын

If you notice, she didn't include the last criteria in the vlookup which is the "True" or "False" that determines the lookup based on exact value or the approximate value. I don't remember which is which but i guess by default it is set to approximate.

@Joseph20203

2 жыл бұрын

@@imserge9559 makes sense

@kayo1265

2 жыл бұрын

@@imserge9559 @@imserge9559 been using vlookuo for over 18yrs and oddly never had to apply a result based on a range like grades in my profession but I'll try this out like in the example and see. Usually the last formula element is based on true/false, 1/0. If left blank I was thinking it would default to a #NA If no match. Again I'll check myself and test.

great lesson

Cool. Now set it up so it'll keep track of all the grades in a marking period and rank the students. One column should be the student number. Then the ranking can be posted in the classroom. We had a basic program to do this when I was in high school in the early 1980s. It ran on a HP2000.

@LeilaGharani

2 жыл бұрын

👍

That's helpful. Thx

You're awesome ♥️

Thank you.

You can use =if(and( for the rank

Sheldon Cooper, Mike Delfino, Gabrielle Solis - are you a fan of Big bang theory and desperate housewives? Anyone noticed more TV series characters?

@LeilaGharani

2 жыл бұрын

Hint: Startrek, Sopranos, Seinfeld - the classics 😁

@ssstudent123

2 жыл бұрын

@@LeilaGharani nice, thanks for the hints ;) but with these series I’m not really familiar with. If it was up to me I would include legen… wait for it …dary - Barney Stinson, Harvey Specter, Oberyn Martell, Kelly Bundy and Tom „Iceman” Kazansky ;) All the best from Poland ! :)

@christophermusso

Жыл бұрын

@ssstudent123 I saw some from Knight Rider, Golden Girls & Roseanne. Don Johnson also snuck in there.

Thanks a lot

Superb❤❤❤😊

I knew vlookup but never knew this function. Thanks a lot

Thanks for sharing

Ma'am, you got my grade wrong. I'm calling mom.

spectacular!

I don't understand how come you don't have to state condition C is higher than G, and it still works?

So if someone gets a 55 do they get a D or an F? Should it be 0-54? Do they just randomly choose if they get that score?

@nenadcimerman5178

Жыл бұрын

It'll be a D grade, because it's equal to the value in cell G3. The values in range F2:F6 are of type "text" and are not referenced in the vlookup-formula, so the don't matter to the result - only description for the user/teacher. However, you're right, that F2 should contain "0 - 54".

Amazing ❤

Now this was useful

Thank you, super useful. I found it only worked if the grade table was sorted ascending. (First try the grades were high-to-low and it didn't work)

She did Kim West dirty

Ease way to do with IF formula

Get it together Sid

@LeilaGharani

Жыл бұрын

😁

Ctrl+E autofill is more exciting

Thanks

@LeilaGharani

2 жыл бұрын

Many thanks!

F4 to fix referencing will change my life.

Thanx

Great! 🤩

I´d use VBA

Fantastic..

Super tric Laila it newly from you only.

Amazing

Good & simple demo nice, how to form if comments pls. Espicialy time chart.

Nice 🙂

Use array formula to fix the copy down.

thanks, i use a nested of on tha

Uao! Ctlr+E and F4 is actually what I learned in this video 😂 U inow I lvoe these videos because even though you may know how to do these this u can still learn a way to make that faster or quicker indeed

The cover 😍

Lovely

Dear Leila, adore your videos and continue to study with them, would kindly ask how you would create a nice chart found in internet but can not add here that image(

These have been so helpful. I haven’t had a chance to watch them all is there one on how to make a spreadsheet that says moves a whole row up if say a column with a date is changed. So that the spreadsheet is sorted by the next date?

I don't think I have a need for this function (yet) ... but I am curious: isn't XLookUp also an option??? Thank you ... thank you ... thank you ... 😍

@LeilaGharani

2 жыл бұрын

Definitely! XLOOKUP and LOOKUP both will work. My personal preference is XLOOKUP because even if we mix the order of the boundaries, the function will still work, whereas VLOOKUP and LOOKUP will fail. I went with this solution because I've noticed many use the IF function for these cases and I know every Excel version has VLOOKUP 😀.

@chh8860

2 жыл бұрын

@@LeilaGharani 😍😍😍

Nice

If there's a merged cells in score(F) then how you may solve this?🧐🧐🧐

You can use if function. As well :D

Use conditional format

I love you, i like your videos 💋💋

Great

OOOOOOWWWWW THAAANKKKSSSSS

How does it work? I mean vlookup throws value corresponding to a match, but here it is picking value from a range (like, for 62, it picked 55 to 79 range automatically).

How can I assign Gpa values instead of F C or D grades?

Why don't you do vlookup with approx value instead ? True instead of false??? No need for a helper column but the data must be sorted

Hey Leila gud day. I can't come up with the correct result, coud u guide me pls.

Sheldon Cooper got the highest grade.

@LeilaGharani

Жыл бұрын

He has to :)