In this VLOOKUP tutorial, you will find a number of advanced formula examples that demonstrate how to look up with multiple criteria, use two VLOOKUP functions in one formula, dynamically pull data from different sheets, and more. Continue reading
by Svetlana Cheusheva, updated on
In this VLOOKUP tutorial, you will find a number of advanced formula examples that demonstrate how to look up with multiple criteria, use two VLOOKUP functions in one formula, dynamically pull data from different sheets, and more. Continue reading
Comments page 5. Total comments: 540
Hi Svetlana,
I am new in Excel and I would like to use codes.
I would like to assign A=1, B=2, C=3, D=4, E=5, F=6, G=7, H=8, I=9, J=0.
if I would enter DJ, the value would be 40
If I would enter FCB, the value would be 632
Your help is appreciated.
Hi!
To change a letter to the corresponding digit, you can use the SUBSTITUTE function.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"C","3"),"B","2"),"A","1")
You can continue this formula with other letters and numbers.
Hi I'm try to make a vlookup with this information .
Category Indicates the category the item belongs to. Item categories are: “Bread & Bun”, “Cookie”, “Cake” and “Pastry”. Each ItemID beginning with the letter B belongs to the Bread & Bun category; those starting with K belongs to the Cookie category; those starting with C belongs to the Cake category; and those starting with Y belongs to the Pastry category.
Hi!
Where do you need help? Explain the problem.
Is there a way to perform a VLOOKUP and have it be case sensitive? For example, our ID's are 11384fMY15KIv and there may be one that is 11384FMY15KLv. Each one is different but the VLOOKUP function will return just one.
Hello!
You can learn more about case-sensitive VLOOKUP in Excel in this article on our blog.
Hi, I am curious to know what will be the formula be if we are looking for 3rd occurrence instead of 2nd occurrence?
Understand that from the formula shown above, +2 = +1 to exclude the first instance and +1 to exclude row 1 with the column headers
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
If the 3rd occurrence is 5 - 6 rows below 2nd occurrence, what will be the formula?
Hello!
This formula finds the third match in the VLOOKUP search:
=VLOOKUP(E1,INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1 + MATCH(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0) +1+ROW(A1))&":A11"), 0)+ROW(A1))&":B11"), 2, FALSE)
hi this work in practise sheet and but able to help in real time solution
I wanted do vlookup in the main file from the different excel files. I need information in the one column in main file. I.e if booking pertains to CC then only CC files needs to be referred in Vlookup and similarly if booking pertains to other type then respective file should be referred for vlookup.
Hello,
I used the vlookup and it works but now i cant move the column to another excel. returns #REF!
please HELP ME!!
Hi!
If you use relative cell references in a formula, they change when you copy the formula. The formula doesn't work. Use absolute references. I recommend reading this guide: Relative and absolute cell reference: why use $ in Excel formula.
I hope it’ll be helpful.
I have been trying to use a vlookup in my spread sheet, but i cannot seem to get it to work correctly. Maybe i need something else?
Data columns on my data sheet are:
(A)Truck #, (B) Date, (C) Miles Driven and (D) Driver Name
What I need to do is:
look up the truck # by date and return the driver name
Any and add suggestions are greatly appreciated.
Hello!
Based on your description, it is hard to completely understand your task.
If you need to find the truck № by date, you can use INDEX + MATCH. Here is the article that may be helpful to you: INDEX MATCH formula to look up from right to left.
If you need to find the driver's name by date and truck number, I recommend using this guide.
I hope it’ll be helpful.
0
I don't know if this is even possible I am going to try to explain it the best I can, I don't know if the "IF" function is the correct thing to use here...
I want to Vlookup From a list in Column A but what I want to look up is dependent on what is in Column B
I don't know how to put this in a formula or even if you can:
If B3=R then =VLOOKUP(C3,Sheet1!$B:$R,11,false) If B2=L then =VLOOKUP(C3,Sheet2!$B:$R,11,false)
Is this possible? Any suggestions how to approach it, is there a better function that I don't know of yet? Thank you
Hello!
If I understood the problem correctly, please try this formula:
=IF(B3="R",VLOOKUP(C3,Sheet1!$B:$R,11,FALSE), IF(B2="L",VLOOKUP(C3,Sheet2!$B:$R,11,FALSE),""))
Here is the article that may be helpful to you: VLOOKUP with IF statement in Excel
THANK YOU! This worked! I was on the right track, saved me a few more hours of trial and error
Hey can u plz tell... is there any shortcut to go to the cells on which our vlookup function is dependent if those cells are in a different sheet.
Hello,
Im trying to find a vlookup that will return when a value is found in the column (when there is no blank). For example:
item
item
item value1
VLOOKUP should return "value1", not 0 or blank. How can I accomplish? Thank you.
Hello!
VLOOKUP function will not be able to solve your problem. Use INDEX + MATCH in formula:
=INDEX(B2:B20,MATCH(TRUE,(A2:A20="item")*(B2:B20 > 0) > 0,0))
You can learn more about INDEX + MATCH in Excel in this article on our blog.
Unfortunately this only returns me #N/A. Is there another way?
Found the issue. It needs to be shift + ctrl + entered :)
Dear Mam
=IF(C8<711,5.12,) this is being charged as working days
Do you an idea that 6.14 will be charged on weekend days instead of 5.12 in the same formula.
Thanks
Hi,
The information you provided is not enough to understand your case and give you any advice.
To check a weekday or a weekend, use the WEEKDAY function.
Is there any way to allocate product based on available production to meet model wise item target with minimum and maximum quantity per day? thanks in advance.
I have 2 excel sheets, both containing EAN of products. 1st sheet has 9000 EANs while 2nd sheet has 7000 EANs. 1st sheet have same 7000 EANs same as in 2nd sheet but remaining 2000 are not common in both sheet. Please let me know how can I find which 2000 EANs are not in 2nd sheet using Vlookup command.
SKU _XS _S _M _L _XL _2X _3XL Total
Os103 10 10 10 10 10 10 10 70
I have a data in this way but i want to convert this excel data this format
SKU Qty
Os103_XS 10
Os103_S 10
Os103_M 10
Os103_L 10
Os103_XL 10
Os103_2XL 10
Os103_3XL 10
I have try to some formula in Excel Like Vlookup and &&
Any one can help ??
I am trying to complete a project for work to calculate BMI and I'm not sure how I should go about it. This is what I've accomplished thus far:
CELL
C2 = height (inches, I used mine which is 77)
C3 = weight (lbs, I used mine which is 210)
C4 = BMI (=703*$C$3/$C$2^2) this returns a 24.90 rounded up slightly with .00 formatting
C5 = BMI Status
C5 is where I am experiencing difficulties. I have a separate table for a BMI chart in the same sheet which encompasses H2:J6. H2:J2 Row are my column headers for the table (BMI Low Range, BMI High Range, BMI Status). Below is my chart data:
Cell
H3 = 0 (BMI Low Range)
I3 = <18.5 (BMI High Range)
J3 = Underweight
H4 = 18.5 (BMI Low Range)
I4 = <25 (BMI High Range)
J4 = Normal
H5 = 25 (BMI Low Range)
I5 = <30 (BMI High Range)
J5 = Overweight
H6 = 30 (BMI Low Range)
I6 = 200 (BMI High Range)
J6 = Obese
What I'm trying to accomplish is taking the calculated result from C4 and comparing it to the BMI chart. The corresponding BMI status in the chart (ie J3:J6) will display in C5.
=IF(COUNTIF($C$4,H3&"<"&I3),J3)& IF(COUNTIF($C$4,H4&"<"&I4),J4)& IF(COUNTIF($C$4,H5&"="&H6),J6)
All it returns is FALSEFALSEFALSEFALSE... with the 24.90 calculated result I have in C5 now it should return at a minimum FALSETRUEFALSEFALSE or FALSENORMALFALSEFALSE right? I'm thinking that vlookup may be what I need but am unsure.
Any help would be greatly appreciated and thank you for your time.
V/r,
James
Hello!
If I understand your task correctly, the following formula should work for you:
=VLOOKUP(C4,H3:J6,3,1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hello, please make a correction:
Formula 2.
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
Needs to be entered as array formula with Ctrl+Shift+Enter
{=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))}
Thx! Nice article!
Hi Alexandr,
Thank you for pointing that out! In Excel 365 that I am using it works as a regular formula due to support for dynamic arrays, and I completely forgot about older versions, sorry for that. I've added a note about Ctrl + Shift + Enter. Thank you!
Use your mouse to enter an IF function that displays a value of “Yes” if the Stock Qty field (cell F5) is less than or equal to the Reorder Qty field (cell H5) and “No” if it is not.
Hello!
Please have a look at this article: How to use IF function in Excel. It contains answers to your question.
Hi,
Do you know how can I use the lookup value of 2000 parameters?
Thanks,
Mariecris
Hi Mariecris,
You can use an INDEX MATCH formula instead of VLOOKUP. It does not have a limit to the size of the lookup value.
i have same reference number- under muliple data's, i should take vlookup in other excel. how to take?pls help
Hello!
If I understand your task correctly, here is the article that may be helpful to you: VLOOKUP across multiple sheets in Excel
I hope my advice will help you solve your task.
How to find same names same code persons are duplicate
Ex:-
1. Naresh 000
2. ABC 212
3. naresh 000
4. ABC 212
Hello!
Here is the article that may be helpful to you: How to identify duplicates in Excel
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hello,
I want to run a daily manning report from an excel roster but don't know how to go about it. I want it to be set up as classifications and Manning Numbers and then have total of each classifications and so it has to provide having personnel showing as on isolations from the current manning as well.
Can you help me?
i have C collumn Col-A,Col-B & Col-C
Under Col-A 4 Data(A1=Name,A2=Ram,A3=Ram,A4=Ram)
Under Col-A 4 Data(B1=Salary,B2=Blank,B3=Blank,B4=4000)
Under Col-A 4 Data(C1=Attendance,C2=Blank,C3=P,C4=A)
Suggest me formula by Vlookup & Index where i can get the data from Col B(Salary) & Col C(Attendance).after Blank.Name is same in A col when run the formula based on Name then form the array provide me 4000 from B col and P from C col. its meance it check data if found blank then move to second and at end provide me after blnk i case of same name.
alwasy provide me data after scape blank cell and swap next row.
Name Salary Attendace
Ram
Ram 4000
Heera A
Ram 3000 P
Heera 2000 A
Heera 1000 A
Ram 500 A
Name Salary
Ram 4000 Result always this
Name Attendace
Ram P Result always this
Name Salary
Heera 2000 Result always this
Name Attendace
Heera A Result always this
Still i dont recive any solutions
Thank you for the detailed instruction for different uses of VLOOKUPs.
I'd like to find out if I could use VLOOKUP to look for a value when a condition applies in the lookup table. For example, =VLOOKUP(Lookup_value_list, lookup_table!A1:D100, 2, false) Under column B, let's say it has certain number of Value As and certain number of Value Bs, I want to add a condition (like a filter to the lookup_table) to ask the vlookup funtion to only look for Bs in the lookup_table. How do I do this without applying for a filter before hand? Thank you in advance!I would appreciate your insights!
Is it possible to import every Nth Cell from another Sheet. I am trying to import every 6th cell from Column C. I’ve tried doing this but I keep ending up with the imported data appearing every 6 rows. I then tried to sort the range this compiled the data but also alphabetised it which I don’t want. Thanks!
Hello,
I wanted to know on how can I pull data for a certain information like "XYZ" from the data provided below.
Item Set Code Test
ABC, IJK, RST, XYZ 5 2001 Major
ABC 2 251 Major
IJK 6 4001 Remission
RST 6 9002 Depression
XYZ 9 12003 Remission
IJK, XYZ 10 8009 Remission
ABC, RST 11 4007 Depression
Thanks
Hello Remejoe!
You can use the VLOOKUP function for searching:
=VLOOKUP("*XYZ*",A1:A17,1,0)
But in this case you will get only the first found match from the list.
To pull all the values by condition, you may try to use a filter in your table.
Please read more about an Excel filter here:
https://www.ablebits.com/office-addins-blog/excel-filter-add-use-remove/
In the single cell we have multiple values like this CAA
CBG
ERT
HGJ
when i am trying vlookup 1st value (CAA) only coming , remaining values not come.
Hello Thavakumar!
For me to be able to help you, please describe your problem in more detail. What values are there in the cells you are applying your formula to?
Are your 4 values typed in the same cell or in 4 different ones?
What formula are you using to look for values?
Please let me know. I think I can suggest a solution but some additional information is needed.
Hi i want use vlookup formula but i have issue to find out result with two lookup value result will be same
as a expamle :
THIS IS DATA
A B C
ORENGE FROUIT
APPLE FROUIT
POTATO VEG
TOMATO VEG
a b
1ST LOOKUP VALUE 2ND LOOKUP VALUE FIND OUT RESULT
ORENGE APPLE ????????
Good day, I am busy with big recons. I want to match info lines (A-X) with info lines (Y-AV) but my key codes is in G and AD). How do I do 'n v-lookup dat say "match code in G with Code in AD, if it matches in the same line 227 it must say yes and if it doesn't match in the same line it must say false. I hope I make sense.
thank you so much.
Hi
I need to vlookup (one column have part numbers) and
(another have part numbers in between - as separating 3 integers).
Eg. 12345678
123-456-78
but both are same.
Kindly assist me how to make it
Thanx
just add CONCATENATE before your vlookup
thank you so much.
you are my hero.
i want get multipal value in sheet like, Amir Have got Amount 5 time and 5 payment are in different Row vloook will got 1st amount and sumif will count All amount but i want like this
1st Amount 151
2nd Amount 515
3rd 120
Hey guys can teach me. If I have entire of Emp Name with staff Id and Date. How am I going to run the VBA excel in a diffent worksheet.
Thank you
All angel
You can simply use this code
Sheets("Sheetname").range("Cell name").value
further i can teach you on team viewer.
+923220000671 is my whatsapp numebr.
Hi ,
Kindly Help With vlookup function of Below Criteria.
column A b
row 1 Type value
Row 2 Apple,mango = vlookup( ?? ) result should be 5
Data
column A b
row 1 Type value
Row 2 Apple 2
Row 3 mango 3
Hi! I have a large database (over 12,000 rows and A to AZ columns) of a name dictionary. It is in constant flux, as some details change or new names come up. I need to prepare for publication a worksheet of all those names that have a certain info in one of the columns. I could do that by filtering, but I would need to stop updating the main database. Can I somehow create a linked worksheet with the names that CURRENTLY have that info but that gets automatically updated if changes (more names or change of info) occur in the main database? I could create a dynamic table and do a Vlookup, but I would get all the empty rows as well, which I don't need.
Thanks for your help.
Hello
I need a vlookup formula which searches for the most correct result.
The situation is as follows. I have a table of hundreds of cities, there cities have specific numbers given. I want ecxell to give me the number of Vienna Oldtown even if I just type Vienn or oldtown.
Thx for your answer
work book1
date card number reg amount
01/01/2019 1076206 60719 80.25
02/01/2019 1076206 60719 13.00
work book2
date card number reg amount
01/01/2019 1076206 60719 80.25
02/01/2019 1076206 60719 13.00
HOW TO RETURN THE CORRECT VALUE TO " WORK BOOK 1", if we used vlookup its return the first lookup value both card number. but it is wrong.
pls help
Hi,
I'm sharing few data as given below..request you to please share the solution for this.
Raw Data Lookup_Value OutPut
NYM1035/323141085 323141085 NYM1035/323141085
1218454/UEI00007463 1218454 1218454/UEI00007463
1219385/UEI00007485
399152/919095
1200053831628 / 1200054080311
399381/919186
918447 / RAHUL.U713051981455
TXPC5141 / 323177477
YASWANTHUPUDI123406121991198/918491
sgumparthi3410061970989/918559
OLESH0000000659482052639/1GPZ070050
QFAM5567 / 323338673
323370031 / RIWR2689
323425520 / BVFG6740
YMUE0863 // 323284372
XKTM6146 // 323295089
5021777833/919207
NFBW0723 / 323276824
399514 // dsankar00615081968136
919240 / kashishkumar201902071989968
397760 / ME.PRITAMGHOSH10061992935
Multiple latest 6 dates from name A1 dates B2 on sheet 1 vertically (Name appears over 20 times with multiple names same quality of times & dates appears over 100 times with multiple dates same quality of times ) to return on another sheet 2 with all information horizontal in formula of latest date / second latest date / third latest date / & so on.
Name is the start A1 to match latest date B2 CRITICAL for rest of information B to AW
Have Changed work sheet 2 with name to start from A1
Oldest Dates B2 start from top of sheet with latest date at the bottom & keep adding to list to bottom
Have used ; =Vlookup(A4,Section!A:B,2,false) — found old date. NOT LATEST
{=Max(if(section!A4:A20000=A4,Section!B4:B20000))}
=Min. As above.
=Arrayformula(Vlookup (A4,section!A4:B20000,2,false))
If I have space between one date to the next date does this matter as I have separated all following dates?
Sheet size is 30 columns A - AW & rows 1 - 50,000 plus will grow in size with added information down the sheet.
I hope you can help with this formula to match dates & 6 latest in order & the information to the right of these dates.?
Regards
Tony
dear, I need help. For Example, IF we have many entries against value "A" than how I can pick the latest date value using the formula in excel. and if I have many entries of different values how I can pick the latest date value. (Sorry For My English)
hey; i need your help; i have a sheet containing 500 types of equipment each equipment has 2 or three prices and i need to get from it the min price , average and max prices and returning the supplier name for each value
Hi, I'm trying to create a v-lookup formula that will look at the corresponding ID in column 1 of sheet1 (which contains my formula), then lookup the ID on sheet2 and bring in the data from column 7 of sheet2 IF... column 6 = 2019. I do v-lookups between sheets all the time and have been trying to put it together with an IF formula but I haven't been able to get it to work. Can you help?
Hi madam,
i refer above all step for my case, but still not found a solution. i have 2 kind of excel sheet. when i select one date in 1st sheet need to get 2 detail from another sheet according to selection data. i try to lot of time & through the every step. but couldn'd. please help me....
Hi,
i am having some product in my master sheet. and i am having month wise sheet like sheet 1= jan,sheet2= fab,...now i enter randomly product name in month wise sheet and his corresponding value. for using vlookup formula i collect corresponding product value from sheet1= jan by(=VLOOKUP(A9,'jan'!B7:F26,5,0). so now i want to collect value for same product in other remaning month and adding value form all month in master sheet. please help
Wonderful tutorials! How helpful!
I've looked for a vlookup formula to add figures with common letter code down columns. Example (Letters col. A, $Values col. B):
AAA $330
AAA $600
AAA $270
BBB $300
CCC $100
CCC $120
On my separate spreadsheet, I would like to add all AAA values in one cell, $1,200; all BBB $300; CCC $220, etc.
Thanks!
Hi,
How can i get different variable with same answer
For example
Name Roll nol
amit 1
vikas 2
ajeet 3
raj 4
manish 5
sanjeet 6
suraj 7
amit 8
My data is given below:
A B C D
Product Rohim korim jodu
TV 100 200 300
AC 301 302 303
FREEZER 3002 3005 3009
After enter the following data I need result (?)
A B C
Rohim TV ??? (result should be 100)
Korim AC ??? (result should be 302)
How can I do it with vlookup or other formula in column C . Pls help.
hi
i am learning excel and i am using a football table,what i`m stuck on is when i enter the weeks results i now want to be able to look for a teams name and be able to bring up its last 5 results to show its current form,i can do this with a pivot table but its time consuming doing every team i was wondering if it can be done to show the whole leagues current form
thanks
colin
Hi,
In the below formula what is the ! symbol for?
=LOOKUP(D26,'B:\HPServices\Current\[Material Price Book.xlsx]Material Pricing'!$B2:$B196,'B:\HPServices\Current\[Material Price Book.xlsx]Material Pricing'!$H2:$H196)
Brian
=IF(ISNA(MATCH(A2, Individual!$A$2:$A$108385, 0)), VLOOKUP(A2, Individual!$A$2:$A$108385, C2&"/"&C3, FALSE)," ")
Row ID Relationship Name
132361 Father Buchi Ramulu
132361 Mother Sujatha
132364 Father Mahesh
132387 Father B.Ramulu
132387 Mother Kondamma
132390 Father Anjaneyulu
132390 Mother Laxmamma
This Row ID should match with other sheet Row ID and return father & mother name "Father/Mother" in single cell.
=CONCATENATE(B2&C2) 132361 Father Buchi Ramulu 132361 Father =VLOOKUP(CONCATENATE(F2&G2),A:D,4,0) Mother =VLOOKUP(CONCATENATE(F2&I2),A:D,4,0) =CONCATENATE(H2,"/",J2)
1. in other sheet, do text to column. Put Unique ID and Father and Mother in col.
2. First look up( concatenate father and id and mother and id) in Raw Data and look up through Lookup.
3. then concatenate mother and father.