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.