What is VLOOKUP? It's a function in Excel that searches a list for a value, and returns a corresponding value.
If you need help to understand VLOOKUP, or you want to see some new tricks to make it do things you didn't think possible, then check out this list of our best VLOOKUP tutorials.
nhoeb
I want learn for my job
Celestino Diaz
GOOD DAY MADAM,
WE CREATE VLOOKUP FROM EXCEL FILE TO ANOTHER EXCEL FILE
HOW CAN WE GENERATE USING XLOOKUP?
THIS OUR EXAMPLE FORMULA OF VLOOKUP THAT WE CAN PULL OUT DATA FROM EXCEL TO ANOTHER EXCEL FILE
=IF(ADMIN_WDataItemCode=””,””,VLOOKUP(ADMIN_WDataItemCode,’C:\Users\USER\Dropbox\ITEM LIST for MRF & TRF\ITEM LIST.xlsx’!ITEMS,2,FALSE))
ADMIN_WDataItemCode = lookup_value
C:\Users\USER\Dropbox\ITEM LIST for MRF & TRF\ITEM LIST.xlsx’ = LOCATION OF THE FILE
ITEMS = table_array
2 = col_index_num
FALSE = range_lookup
HOW CAN I USE THIS TO XLOOKUP
I WISH YOU CAN HELP ME
THANK YOU VERY MUCH FOR ALL I LEARN A LOT MADAM…
CELESTINO SWANSON DIAZ JR.
Mynda Treacy
Hi Celestino, please see this XLOOKUP tutorial.
Karimi
Hi, please, I want help I want the process of extracting an ancient history from among several dates by ecxel
Mynda Treacy
Hi Karimi,
Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
dian
Hi, Mynda ๐
Thank you very much, your tutorial really helped me to explore ecxel
Regard
Mynda Treacy
So pleased to hear that, Dian!
Athar khan
Dear,sir/Madam i learn excel v lookup formula
Mynda Treacy
Glad we could help, Athar ๐
Ravindra
You are great. Liked the way you explain things. Thanks.
Mynda Treacy
Glad we can help, Ravindra ๐
Liz
Okay I need some help on a VLOOKUP to make something a little easier.
I have two worksheets within a workbook for which I have a VLookup working.
It takes the Sheet 1-1 and Sheet 1-2 and Adds them in the Vlookup worksheet named Total Sheet 1. However I need another 50 work sheets 1-1 & Sheet 1-2 and 50 more Vlookup Sheets with the totals. So for example the next set of sheets within the workbook will have 2-1 and 2-2 with a summary total worksheet 2 that Vlookup sums the 2-1 & 2-2. However when copying the worksheets they will copy the Vlookup from 1-1 & 1-2 and then I have to manually change the formula to correctly look at the correct worksheet to do the Vlookup on. Is there a way to not manually have to type in the formula to change sheet 1-1 and 1-2 to 2-1 and 2-2? Thanks!
Catalin Bombea
Hi Liz,
Try the INDIRECT function, you will be able to build the sheet name using ROW() references.
David Bill
I have a list of the 159 counties in Georgia(US) divided into six recruiting territories. I want to be able to enter a county name of the county and populate a field with the assigned territory number and the recruiters name.
Mynda Treacy
VLOOKUP can definitely help with that. If you get stuck, please post your question in our Excel forum where you can also provide a sample Excel file.
Stewart Booth
Require training on what is and how to use VLookUp.
Mynda Treacy
Hi Stewart,
Just work your way through the articles on this page.
Regards
Phil
jitendra
required help regarding vlookup file x colum b data required from file y. refrence number of colum a both file same, I required data file from file y colum b to file colum b
Catalin Bombea
Hi Jitendra,
To get data from another file, it’s better to use power query instead of formulas, some functions are not working if the source file is closed.
Rafiq
I want to distribute values in one table having multiple items with single item values in the form of FIFO basis. but value should be not more then reference table in consolidation
1st Table
Item party value (reference value)
A A1 25 25
A A2 24 24
B B1 20 20
B B2 15 10
2nd Table
Item Value
A 50
B 30
What is the procedure to distribute 1st table’s reference value from 2nd table value.
Mynda Treacy
Hi Rafiq,
It’s tricky to help without seeing your Excel file structure. Please post your question on our Excel forum where you can upload an Excel file with your sample data and desired result. We can then help you with the solution.
Mynda
Rosy
Mam,
I feel very confused. I always have problem in understanding V lookup.But this is very important formulae to learn. Could you please help me out in using this function.
Thanks!
Mynda Treacy
Hi Rosy,
Please work through the first tutorial linked to on this page. Then if you’re still confused, please post your Excel file with your attempt on our Excel forum where we can see where you’re going wrong and help you further.
Mynda
Tea
Hello,
I am looking to use a vlookup formula to see if there has been an increase or decrease when comparing one number to another.
Please help!
Thanks!
Philip Treacy
Hi Tea,
Without seeing your data it’s difficult to give a response. From your description why can’t you use something like IF(A>B,0,1) ?
If you start a topic on our forum and supply your workbook and data it’ll be easier for us to give an answer.
Regards
Phil
Vipul
I want vlookup support for multiple value.
Mynda Treacy
Hi Vipul,
There is a link for this tutorial on this page.
Mynda
Maschshalieen Van Eyssen
I would like to figure out an efficient formula or excel formula to calculate FV month to month for multiple sheets at same time; the difficulty is that the additional deposits are at random days during the month and I can only find formulas to calculate regular deposits either at the start or the end of the month. For example if I deposit during June 15th but then the client withdraws on 17th june then the daily calculated interest will adjust for the remainder of the month to the increased principle, and therefore the interest will be higher for the remaining 13/14 days of the month. Is there a formula to avoid having to create a day by day spreadsheet? One where I may be able to enter into the formula the specific day of the month and the deposited amount to adjust interest daily interest calculations for the remainder of the month.I work with multiple investment accounts within our 3rd party and currently i calculate day by day and do adjustments manually.
I know there is a way to do it all automatically every month end and adjust prime interest rate by click of one button. Its just so time consuming,
Can you please help.
Catalin Bombea
Hi,
It should be possible. Most of the problems come from keeping data split into multiple sheets. Once you merge the data into a single sheet, there will me much less problems. You can do that with Power Query.
There may be other solutions, but we need to see a sample file with manual examples of the desired outcome. Upload a sample file on our forum, create a new topic after sign-in.
Catalin
Rick
I have a Excel file(original) with part numbers, description and prices. We have added columns for minimums and location.
ok now prices have changed, so I now have another spreadsheet with more part numbers and updated prices. I need to get the new prices, descriptions and prices to the original, without the “Min” & “Loc” columns’ rows shifting when new part numbers are inserted
Mynda Treacy
Hi Rick,
Please post your question on our Excel Forum with an example file. I’m not sure what you mean by ‘without columns rows shifting’.
Thanks,
Mynda
Jim Gardzio
HI!
Do you have a course that covers this? I would buy that.
Mynda Treacy
Hi Jim,
Sure do, our Excel Course covers VLOOKUP, among other ‘must know’ formulas.
Mynda
Raj Kumar
can i have this formula in Microsoft excel
Catalin Bombea
Of course Raj, you can copy it and paste it in an excel cell ๐
You will have to adjust the ranges to your data range. If you need help, use our foum to open a new topic, you can upload sample files if needed.
Catalin
Johnny
Hi Maam Mynda Tracy
I need your help!
can i have this formula in Microsoft excel
REGULAR TIME 8:00AM – 12:00PM
Here the situation:
time in 8:45 AM
time out 12:00 PM
Result: 0:45 minutes late
Can you help me?
Catalin Bombea
Hi Johnny,
The formula should be built like this(the paranthesis are important, use them as below):
=(Regular End Time-Regular Start Time)-(time out-time in). This will be equal to: 4:00-3:15=0:45
Catalin
Dritan
You have done a great job, and very helpful to others it’s perfect.
Philip Treacy
Thanks Dritan
saursfts
Hi mynda I am working vba and MS excel making dashboard
as beginner would need all those dashboard which were posted on your website how can i get it can you mail me some excel dashboard need to learn it
Mynda Treacy
Hi Saursfts,
Thanks for your interest in my dashboards. I include them in my Excel Dashboard course.
There is also one here you can download for free.
Kind regards,
Mynda.
Dritan
Dear Mynda Treacy,
I have try to use formula to extract data from a tabel and place it to different sheets but i have fail to do that, I use vlookup and iF function but I couldn’t make it (some of the data are the same).
I can send you the file so you can understand the situations.
What formula you suggest me to use?
Thanks,
Dritan
Catalin Bombea
Hi Dritan,
If you have duplicates, this tutorial will help you: lookup-and-return-multiple-matches There is a sample file you can download and test.
You can use our Help Desk to upload a sample file if you still have problems. A detailed description of what are you trying to do will help us understand your situation.
Cheers,
Catalin
Ateny Ajak
I love Online Training Hub Tutorials and I’d definitely recommended to my friends.
Mynda Treacy
Wow! Thank you, Ateny ๐
David
I have a recurring problem when pasting values into a spreadsheet from a database query (Hyperion especially). A vlookup (already in place and working) using the pasted values won’t work until I edit each value in the formula bar and hit ENTER. Then the vlookup works.
I can read the values and execute other functions on that value. It just won’t work for vlookup.
Any advice would be appreciated.
Does anyone know a
Mynda Treacy
Hi David,
Have you tried pasting the values in with Paste Special > Values as opposed to just pasting them in using CTRL+V or right-click > Paste?
Another option is to run them through the Text to Columns tool, or use a formula like TEXT for text or VALUE for numbers to fix the format of the cells.
Let me know if any of those options doesn’t work. If you can send me an extract of your workbook and the formula not working via the help desk, I can take a look at it.
Kind regards,
Mynda.
MARY
Hello Mynda,
My Problem:
I want to Lookup the Code in Workbook1 that is located is listed in Workbook2,
then I want to Match the Qty Dispense in Workbook1 against what is listed in Workbook2,
finally, if the Color-Code and Qty Dispense match, I then want to return the PRICE from WORKBOOK2 to cell “C” in Workbook1.
Hello Mynda,
My Problem:
I want to Lookup the Code in Workbook1 that is located is listed in Workbook2,
then I want to Match the Qty Dispense in Workbook1 against what is listed in Workbook2,
finally, if the Color-Code and Qty Dispense match, I then want to return the PRICE from WORKBOOK2 to cell “C” in Workbook
WORKBOOK1 WORKBOOK2
CODE1 QTY DISPENSE PRICE TAB1 TAB2 TAB3 TAB4 TAB5
CODE1 CODE2 CODE3 CODE4 CODE5
RED110 5 $3.00 RED110 PURPLE7 ROSE10 BLUE2
YELLOW99 100 $4.00 5 70 120
ROSE10 70 $5.00 $3.00 $5.00 $5.00
LILAC11 25 $3.00 10 140 240
BLUE2 120 $5.00 $6.00 $10.00 $10.00
PINK3 30 $3.00 5 70 240
PURPLE7 120 $4.00
ORANGE9 24 $3.00
MARY
Additional Question:
Can I do a multiple MATCH? Example:
VLOOKUP(A2&H2, INDIRECT(“‘”&INDEX(SheetList,MATCH(1&2,–(COUNTIF(INDIRECT(“‘”&SheetList&”‘!$A$1:$D$25″),A2)>0),0))&”‘!$A$1:$D$25”), 3,FALSE)
My VLOOKUP values work (A2&H2), it recognizes my value.
But, my MATCH values are not being recognize. What is the proper syntac “MATCH(1&2, “?
Mynda Treacy
Hi Mary,
The MATCH should work like that. Are you sure the values are present in the lookup range? i.e. 1&2 will become 12 as text not a number, so it will be looking for a value formatted as text too.
Try this:
Type ’12 in a cell B1 (the ‘ formats the 12 as text and will only be visible when viewed in the formula bar). Then in another cell enter this MATCH formula:
Your result should be 1. Now if you type the number 12 in B1 you’ll get a #N/A error. Hopefully this will help you understand the difference.
Kind regards,
Mynda.
Mynda Treacy
Hi Mary,
I had a look at this last night but I couldn’t follow the data as the comment has squashed it all together. Are you able to send me your workbooks via the help desk so I can take a look?
Cheers,
Mynda.
Vic S
I am working on a job costing worksheet where I enter data from my field workers on a daily basis. I enter worker rates of pay, material prices per unit, equipment use by type and rate/hour (or day). I incorporate lookup tables to get my prices and then summarize my totals at the end of the day. This system works well for one day jobs. I am trying to create an efficient means to total costs for jobs exceeding one day. Jobs can take anywhere from one day to several months. Any thoughts on how to organize and total data when the number of entries differs by job?
Carlo Estopia
Hi Vic S,
Your description seems too broad for me.
However, If I’m not wrong, your problems are more on not having unique figures
to make VLOOKUP work, right? Well, the best way to do that is to add a column
that would create uniqueness when combined to your existing values (in your table)
to be looked up. I am just guessing of course. So please send a mock file and we’ll study
and provide better solutions here: HELP DESK.
Cheers,
CarloE
SANTHOSH
I am trying to check the common data in the two fields with same name,but vlookup does not picking the same ones.Please assist me why the vlookup function does not retrieve when comparing two fields.
Mynda Treacy
Hi Santhosh,
If it cannot find a name that you are certain is in the list then it is likely to be a subtle formatting difference between the two cells. Often one field is formatted as text and the other is not. Press F2 to edit the cells and check if there is an apostrophe at the front of one cell and not the other. Alternatively it might be a case of trailing spaces.
If that’s not it then please send me the file and I’ll take a look.
Kind regards,
Mynda.
Essam AlKhaldi
Essam AlKhaldi
Dear Eductor Treacy:
Having just sent an E-mail, I feel I must thank you so much for Your Humanitarian Program,
Times and Again,
Thank You Educators, Treacy,
Essam AlKhaldi
Hi Tutor Mynda,
May I request Your Guide Lines for the limits to which moderation might
suit your Good Sense!?
Thanks in advance
Mynda Treacy
Hi Essam,
I’m sorry I don’t understand your question.
Kind regards,
Mynda.
Mynda Treacy
You’re welcome, Essam ๐
BTW I haven’t received an email from you.
Kind regards,
Mynda.
Asha
I want to learn Vlookup. can you provide me the details how can i learn any cd or demo is there.
pls do the needful
thanks.
Mynda Treacy
Hi Asha,
You can learn VLOOKUP from the links on this page or you can join our Premium training and get access to the video tutorials.
Unfortunately we don’t send CD’s of our training.
Kind regards,
Mynda.
รlafur Gunnarsson
How to folding time in excel example 7:30 + 25:25 + 13:27
Mynda Treacy
Hi Olafur,
Put each value in it’s own cell and then SUM them. Format the cells with custom number format [h]:mm to ensure the total displays correctly.
Kind regards,
Mynda.
Dattatreya Kanchagar
Dear Sir/Madam,
I am very much interested to know to learn vlookup formulas so please provide the same.
Mynda Treacy
Hi Dattatreya,
You can find a list of our VLOOKUP tutorials on this page. Simply click on the links above to view each tutorial.
Kind regards,
Mynda.
sabiha
I keep getting N/A in my V look what could be the reasons. I checked and my cells are all the same no apostrpphy or text
Mynda Treacy
Hi Sabiha,
You get an N/A error if the value you are looking up doesn’t exist in the range you are looking up.
Kind regards,
Mynda.
Becky
I’d like to offer some advice. We use VLOOKUP every day. Data from one source will be formatted differently than from another. Often the problem is the data that appears to be numeric is actually text. We have tried formatting both sets of data exactly the same, still get the N/A. To resolve, we multiply the data by 1 with the result in a blank column, then copy paste as values back into the original cells. Seems to force Excel to recognize the data as numeric. We have also found trailing spaces in some of our text data (poorly designed database). We use the “trim” formula to remove the trailing spaces.
Mynda Treacy
Hi Becky,
Great tips. Thanks for sharing.
You could also add 0 which would achieve the same thing. Alternatively you can use Text to Columns to fix the data format. All work.
Kind regards,
Mynda.
PAM BECKERT
I need to get a percenile from a non numerical list. Example: Out of 50 entries showing facility in column F, what percentage is SJMC. (all other entries are various facilities.)
To add to my dilema, I have to then break it down by month and yearly quarters.
HELP!!!!!
Carlo Estopia
Hi Pam,
Use Tables for better result. You can simply highlight your data and Go to Insert, Click Tables.
Now let’s Just assume that your dates are in column E and facilities in column F. Note: I have excluded the headers
from the count.
You may use a formula like these:
All items
Monthly
Yearly
The difference between the yearly and monthly formula is merely the parameter dates.
More on Tables, Countifs
Cheers,
CarloE
HENDRIK
Can you please show me how to calculate percentage where lets say i have several material stored on shelves but would like to caculate in percentage how mush spaced is used and how many unused.
Mynda Treacy
Hi Hendrik,
Space used / total space = % space used
space unused / total space = % space unused
Kind regards,
Mynda.
Samsino
Hello Mynda,
your approach is just wonderful.
Philip Treacy
Cheers, Samsino ๐
Prasad Sakpal
Hi,
It’s really very good link and provide the perfection in learning.
Thank you very much….
Mynda Treacy
You’re welcome, Prasad ๐
Supriya Arjugade
Dear Madam/Sir,
I want to learn Vlookup. can you provide me the details how can i learn any cd or demo is there.
pls do the needful
thanks.
Mynda Treacy
Hi Supriya,
There are several links on this page to VLOOKUP tutorials. Our training is all online. If you want to take a video course you can find out more and sign up here.
Kind regards,
Mynda.
Pavan
I have to columns with different Column Names but the data in 2 columns are same .
Ex Col1 has 182 row information and Col2 has 208 row information.
Comparision needed
Col1=Col2
Col1!=Col2
Col2!=Col1
Mynda Treacy
Hi Pavan,
I’m sorry I don’t understand your question. Can you please send me an example Excel file via the help desk.
Thanks,
Mynda.