Excel Lookup functions, explained: XLOOKUP / LOOKUP / VLOOKUP / HLOOKUP [IGCSE ICT 0417]

Подписаться 9 тыс.

Просмотров 22 тыс.

50% 1

Learn all about the Lookup functions, where and how to use them, their advantages / disadvantages and where each one falls short. Why was the XLOOKUP function created and how to use it. In this tutorial i also cover the new SPILL feature. The tutorial is covers all the Lookup functions required for the Cambridge ICT 0417 practical exam paper 3.
0:00 Introduction
0:52 Understanding what a LOOKUP function is used for
2:43 LOOKUP function Vertical lookup
8:32 LOOKUP function Horizontal lookup
10:38 VLOOKUP Function Approximate Match
17:37 HLOOKUP Function Approximate Match
19:37 Where do VLOOKUP and HLOOKUP fall short
23:21 VLOOKUP Exact Match
26:33 HLOOKUP Exact Match
27:34 Using the result of the Hlookup in a calculation
28:19 Using Multiple Workbooks and Spreadsheets
33:37 Why the XLLOOKUP function was created
36:21 XLOOKUP Function vertical lookup
41:20 SPILL feature
43:10 XLOOKUP Function horizontal lookup
Link to the tutorial on References
• Excel References, Rela...

Опубликовано:

6 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 97

@cinnap00fle 3 месяца назад

I’m learning 1 years worth lessons in 5 days this dude is working wonders! Istg if I pass I’ll make an entire speech praising him ❤

@paphitisn 3 месяца назад

thanks for your comment. You better start preparing that speech because you are going to do great. Good luck

@cinnap00fle 2 месяца назад

@@paphitisn hehe thanks! I did my paper 2 two days ago and I didn’t have enough time for the PowerPoint presentation I was really disappointed cuz I seemed to know everything but didn’t have enough time but it was only 18 marks so I hope it doesn’t impact my paper that bad! I also had my paper 3 today and I was raging through your lookup videos 5 mins before my paper started 🥹 I did surprisingly better than last time, thanks to you sir!

@paphitisn 2 месяца назад

@@cinnap00fle That is a shame about paper 2 but don't worry about it know. Paper 3 sounds like it went well for you so that is good. Focus on the theory paper now. I suggest you work through the past 2023 theory papers. You can find them online together with their marksheets. Remember, do not just read the marksheets but take time to think of the answers before.

@KareemAfham Месяц назад

@@cinnap00fle u shouldd pls :)

@afreeeeeen 2 месяца назад

my exam is tmr and you’re saving my life rn THANK YOU SO MUCH

@sophiee4130 2 месяца назад

Paper 3 gang 😭

@SpamAccount-gh3ev 2 месяца назад

@@sophiee4130 same 🥲 good luck guys

@manariii4545 2 месяца назад

Literally

@khalidyahia8701 9 месяцев назад

I cant believe that I found such a thorough explanation of Excel for free.

@paphitisn 9 месяцев назад

I am glad you found it useful. You will find all my tutorials are thorough and have detailed explanations (some would say too much detail). The purpose of this channel is to provide great educational resources. Thankyou for your comment.

@lilacmoondust 4 месяца назад

you explain better than my ict teacher and she's the head of the ict department...

@paphitisn 4 месяца назад

I am glad you found this useful.

@zenn1047 3 месяца назад

me too!!! 😭

@abdulazizmohammed2515 Год назад

ive watched yahmads ict videos regarding the lookup functions and they were very difficult to understand But the way you demonstrate and explain the examples and how to do them is extremely useful and I was only able to understand this topic thanks to you so thank you so much man I also really really really appreciate the amount of effort you put into your videos so for the third time now thank you so so so much

@paphitisn Год назад

I am so glad that it helped you. Thank you for coming back to leave your feedback

@user-zt3on8gw6l 2 месяца назад

Hello sir! Thank you so much for your videos! I just did my paper 2 the other day, and your voice was flying around my head for the whole 2 hours and 15 mins. Please keep supplying us with these videos, sir, you really are a godsend!!!!!!!!!!!

@khawajamohammadayaanali9795 3 месяца назад

Thank you so much. You helped me prepare for my exams in the recent days when i had so many things unclear. I am well prepared thats to you🙌

@SilinaLibzo Год назад

Was interested in what Xlookup was, came here and knew stuff that I had a hard time comprehending before. This video is more helpful than anyone thinks. And from this, I could finally understand the lookups that I seemed to solve on without quite grasping the logic behind. I finally got to understand the difference between lookup and the rest. Thank you so so much.

@paphitisn Год назад

Thankyou for your comment and kind words. Your feedback is appreciated. I am glad that you found this tutorial useful and that it cleared up the lookup functions for you.

@potentialofprotonis3 5 месяцев назад

wish me luck! paper in 2hrs! tqsm for saving my life

@paphitisn 5 месяцев назад

Best of luck!

@potentialofprotonis3 5 месяцев назад

@@paphitisn it went great!!

@paphitisn 5 месяцев назад

@@potentialofprotonis3 awesome job well done. i am happy to hear it went well for you.

@SpamAccount-gh3ev 2 месяца назад

Thanks Nicos! I have my paper 3 tomorrow and your videos have been so helpful. If you ever have the chance, I'm doing AS -Level IT next year and it would be so helpful to have A-Level content videos

@MrGaming-ut5uy 2 месяца назад

Hello I did my paper 2 today and thanks to your help i finished in 1h30m and I knew how to solve everything thank you so much I have paper 3 on Thursday I’m gonna be watching your excel videos too

@paphitisn 2 месяца назад

That is awesome! Well done. Good luck with paper 3

@vsshihad 2 месяца назад

me too i have p3 on thursday ,

@glyzer 2 месяца назад

I could not do my paper 2 due to rain, Cambridge had to cancel it.. but I have my paper 3 tomorrow! Hope I pass

@Nonchalant-pka 2 месяца назад

@@glyzer i did my paper 2 even though it rained, it didn't bother us much

@paphitisn 2 месяца назад

@@glyzer I am sorry to hear that. Things must be hard where you are now, i pray for you all and hope things get back to normal soon. Good luck with paper 3

@jennie08._ 2 месяца назад

Thank u so much Nicos! My ICT practical official paper 2 IGCSE exam is on 16th of April...pray for me :)

@paphitisn 2 месяца назад

Praying for all of you. Good luck. remember to keep a cool head. Dont panic, there is always something new. Focus on doing everything you can and manage your time well. If something is new and you are not sure what to do, leave it and move on. Better to do everything and then come back to it if you have time. Make sure to check your printouts.

@jennie08._ 2 месяца назад

@@paphitisn Hi Nicos! I've been doing past paper, recently did 2 paper 2's of ict, and am aiming for about 2 more. I'll do 1-2 extra before the exam, but spend an hour beofre the exam relaxing. Thanks for all the resources, keeping my chin up :)

@paphitisn 2 месяца назад

@@jennie08._ Sounds like you are well prepared. I would allow yourself more than an hour to relax. Dont burn out.

@jennie08._ 2 месяца назад

@@paphitisn Hey Nicos, the ICT p2 got cancelled where I'm at due to the weather conditions and the intense amount of rain...therefore we'll be graded in terms of our performance in paper 3 and theory.

@paphitisn 2 месяца назад

@@jennie08._ Your teacher should also send a predicted grade for paper 1. I hope and pray that tings where you are get back to normal soon.

@abeerabbas2046 2 месяца назад

exam tmrw u helped out tons

@ShubhaShrinivas Год назад

very well explained for students. Thank you Mr. Nicos. Continue the good work sir.

@paphitisn Год назад

you are welcome. thanks for your feedback

@yusramohamedsherif7738 4 месяца назад

this is awesome ,thanks

@paphitisn 4 месяца назад

I'm glad it helped, thanks for your comment.

@merirautanen5495 Год назад

For the XLOOKUP's match mode feature, is there a way to display the closest number instead of the bigger or smaller one? Also, what is the wildcard function used for?

@someoneyk6165 Год назад

I binge watched excel videos just to get sum and conditional formatting in the exam🤓

@paphitisn Год назад

The journey is what counts. How much have you learnt? That is what is important. Well done and let's hope your grade will reflect your efforts.

@KobeEla-df2my 2 месяца назад

You are the best

@paphitisn 2 месяца назад

Thank you for your comment

@MIMGURU 2 года назад

Super

@sarahtyara2808 Год назад

Hello ... Mr. Nicos ... could you teach me of MOD function and Row function ?

@Saif-cc5jq 11 месяцев назад

Hi there! Thank you so much for your videos, would love it if you did videos for website authoring but in web expressioin 4.

@paphitisn 11 месяцев назад

I am glad you like them. I have tutorials on HTML and CSS but using Dreamweaver as that is what my students use for the ICT IGCSE.

@user-qg7jx3ci8m 2 месяца назад

can someone help me how to decide which lookup to put when? also when to use true or false!!!

@kshamakadam5287 6 месяцев назад

Your vids are v helpful.... btw is it ok to use Xlookup function every single time for every situation instead of v lookup, h lookup or lookup? Kindly answer

@paphitisn 6 месяцев назад

XLOOKUP is mentioned in the syllabus so you can use it to replace the other lookup functions.

@gs37378 9 месяцев назад

you are just amazing

@paphitisn 9 месяцев назад

Thank you :)

@gs37378 9 месяцев назад

you are welcome sir@@paphitisn

@gs37378 9 месяцев назад

mister nicos please think about my request because my exam is near and i cant find anyone as good as you are :( @@paphitisn

@souvlaki111 2 месяца назад

god bless u

@la670 Год назад

Thank you, but did they ever ask for an approximate match before?

@paphitisn Год назад

You mean a range lookup? Yes, in fact in one of the recent papers. There was a question on cameras and their housing. You had to find the discount percentage from a range lookup based on the total cost of the camera and housing. I can find the exact paper if you would like to give it a go. It's a 2021 or 2022 paper

@Aymannnnnn_ 2 месяца назад

Why do we add a dollar sign in each function?

@susu9074 3 месяца назад

Dear sir, How can i get those Excel resource files ? Thanks

@paphitisn 3 месяца назад

i dont keep the resource files but you can simply duplicate them

@legendmemer384 3 месяца назад

Pls reply for my XLookup question

@legendmemer384 3 месяца назад

How do u know when to do approximate match, it is extremely difficult to recognize in papers, can you give an example of a paper question

@paphitisn 3 месяца назад

This is clearly explained here with example. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-dbCvU1zkoPM.html

@tasneemkhalid284 Год назад

hi sir how do i know whether to use v lookup or just lookup? and why not just use lookup?

@paphitisn Год назад

Lookup will always use the first column for the lookup column. If the lookup collumn is to the right of the column you want to return an anser from, you can not use vlookup. You must use lookup or th Xlookup.

@tasneemkhalid284 Год назад

@@paphitisn ooh ok thank you!

@manya6851 2 месяца назад

what is the difference between lookup and vlookup function. How do we know which one of them to use

@paphitisn 2 месяца назад

This is clearly explained in the video. Vlookup always uses the leftmost column of the array for the lookup column and you indicate which collumn to use to return a value. Th lookup you indicate the lookup column and the return column separately. When to use which? if the lookup column is to the right oof the return column you can not use a vlookup, you must use a lookup.

@legendmemer384 3 месяца назад

Also is there any paper in which XLookup was used, can we use XLookup to display an error message

@paphitisn 3 месяца назад

XLOOKUP is not mandatory. They will not give you a problem that can not be solved with vlookup, hlookup or lookup. You can however opt to use Xlookup if you like.

@legendmemer384 3 месяца назад

@@paphitisn no like for example there was a paper in which they were saying to display an error msg if nothing is there, now mostly youtubers were using a big big command of hlookup but I think u can do it easily with XLookup, what do u think

@paphitisn 3 месяца назад

@@legendmemer384 that is fine as Xlookup is mentioned in the syllabus

@sophiagrace1630 Год назад

how do i know when i need to use a lookup function or vlookup. does it make a big difference wich one i use??

@paphitisn Год назад

Hi Sophia. Lookup is only for numeric lookup but it allows you to choose the lookup column and the return column as separate ranges. The Vlookup will work for numeric and text lookup. However you chose a table and the lookup column MUST be the first column in the table selected and the return column MUST be to the right of the lookup column. SO essentially if the lookup column is column number 3 and the column with the return values is column number 2, you can not use the vlookup function unless you change the table and move the lookup column to be the first column in the array you select. Hope that makes sense. if not let me know and i will record you a small demo.

@alishbanaeem5862 5 месяцев назад

record a small demo@@paphitisn

@ExoticAnimation Год назад

wud like if had access to files cud practice wud be nice

@paphitisn Год назад

i don't have the files i used in the tutorials to send you but the data in the spreadsheets i used is really quite minimal so you could easily duplicate the data in a blank spreadsheet.

@maya_008 2 месяца назад

has xlookup ever came and is it necessary to learn it?

@paphitisn 2 месяца назад

Xlookup is not a required function but you can use it if you want as it is included in the syllabus. Its up to you if you want to use it instead of using Vllokup, Hlookup and Lookup

@maya_008 2 месяца назад

@@paphitisn okay thankyou so much, your videos are really helpful!

@user-hb4gc3sg6j 2 месяца назад

why dont you use fx so its easier too see the criterias

@paphitisn 2 месяца назад

You need to understand the functions and the syntax to add the criteria so that you can modify functions as you like. You will also understand how functions work. Using Fx wizard does not teach you this. You will find yourself in a situation where you know what function to use but not know how to put the function together, especially when nesting functions.