oh my gosh, thank you so much for your answer to my excel question!!! it worked perfectly - my spreadsheet looks ✨delightful✨ and you have saved me so much time. vlookup had cropped up in my searches but all the explanations of how to use it were so confusing. you deserve all of the beautiful seaside excursions ever, thank you!!
YAY 🥳🎉🥳🎉🥳 I AM SO GLAD! I love excel but I have sadly become too senior at my job such that I no longer build models and files myself so any time I get to geek out about it I feel very happy 🥰
For that reason let me also offer a way to make vlookup a little more dynamic (under the cut)
Specifically, what if your long list had many columns and you did not want to count them to see where price showed up? Or perhaps it was a data table that was being edited and you thought someone might add or delete a column which would make your count be off (if someone adds a column to your og table the og formula absolutely will not update from e.g. 2 to 3 in terms of what column you want it to look up). Is all hope lost?
No! Ignoring the part where I accidentally deleted apples off our long list, instead of typing in 2 for the column number, you can make excel find what column the variable "price" occurs, by substituting in a nested match() formula.
MATCH() takes three arguments:
What do you want me to find? Here price which in my small table in cell B$9 - and note the way i fixed what row this reference should be on this time as we copy and paste the formula by putting a $ only before the 9.
Where do you want me to find it? Here the answer is the first row of our long table, which contains the names of the variables. Once again, this can very well be in another tab. We don't want this reference to move at all when the formula is copied over so it's fixed with two $$s.
Can I, MS Excel, vibe ™️ in how I go find this match 🥺👉👈? The answer to this question is still an absolute NO which in this instance corresponds to zero.
If you type this formula into its own cell and hit enter you will see that it returns the number 2. Thus, when you nest it into the main vlookup formula it does what we previously did by hand dynamically. Voila! 🎉
hi Alex!! i hope you don't mind, but if i have an excel question 👉👈 basically i have a long long list of items, each with its price. i want to make smaller lists of different combinations of items, and have it auto-populate a column with the prices for each list. if i have the full list of each item with its price, is there a way to get my smaller lists to auto-populate with the price from this?? hope this makes sense and sorry for the random ask 😭😭
Please it makes my day every time I get an excel question 🥰🥰🥰
It sounds like what you need is the =VLOOKUP() function which can "look up" prices from the long list to populate the shorter list. Here is a mock up:
I have a fake long list in cells A1 thru A5 and a fake short list in A9:10. To find the price for mangoes I entered the vlookup formula into cell B9. It takes 4 values (separated by commas in the above- if you are using excel in a different language the separator might be something else)
What do you need to be looked up? Here the answer is a mango, which is entered into cell A9, so I said A9. The $ in front of the A tells Excel to fix the column reference. In other words even if you copy the same formula to cell C9, it would still reference column A, not B (which is what it would have done sans fixing)
Where do you want this to be looked up? The answer is the long list so I gave the full range for it. The $A$1 notation fixes the entire range, so regardless of what column or row you copy your formula into it will keep referencing the same range.
Which column's values do you want me to return? Here price is in the second column of the referenced array so we say 2. If your second column contained e.g. colour and prices were in the 3rd, we would have made our reference range $A$1:$C$6 above and said 3 here.
Do you want me to look things up based on the vibes? 😎 NEVER trust excel to vibe so we say false, i.e. give me an exact match. Idk what's up with the mobile app but in my computer I don't enter the () after false.
You can then hit copy with the cell selected and paste the formula into the rest of your small list and voila, prices!
Vlookup can look up values across different tabs and it can be made more dynamic than the simple example I have above. There are also other more complicated formulas that are even more dynamic. Conversely all this formula business can be quite confusing when you are new to it. My DMs are fully open if you have questions, follow-ups, or the above does not work for you!
For further reference here is the formula for grape:
On my computer, when I had just written the formula in B9 and hit enter, I would have selected B9 and B10 and then hit Ctrl + D which would drag the formula down to B10. As an alternative to copying cell B9 and pasting it onto B10.
برنامج Excel لن يموت: جدول البيانات الذي ساهم في إطلاق مليون شركة ناشئة
برنامج Excel لن يموت: جدول البيانات الذي ساهم في إطلاق مليون شركة ناشئة
هذه التدوينة برعاية «شيء من حتى» مجلة منوعة تحوي مقالات وقصص وروايات مسلسلة يكتبها عامر حريري
مجلة شيء من حتى
لدعم محتواي وطلب الرعاية تصفح ملف رعاية محتوى يونس بن عمارة وتواصل معي واتساب.
برنامج يرفض أن يموت وآخذ في الازدهار وعلى أكتافه نشأت شركات كبرى
سنتحدث اليوم عن برنامج عتيق ظهر منذ 36 سنة مضت لكنه لا يبدي أي علامات للتقهقر أو المَوَتَان 👇
أُصدر برنامج جداول بيانات إكسال من مايكروسوفت…
