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 15. Total comments: 540
Saved me hours of formula testing and frustration.
5 mins of reading saved hours of vlookup frustrations.
Thank you very much
this is relay very help full for me thank your dear Svetlana Cheusheva.
i have a worksheet with various data, i want to transfer some selected columns and multiple rows at the same time in other worksheet using vba
Hi,
I have a data of staff name, date and daily productivity.
Data catagory as "Actuals"
A B C D E
Date Name Productivity % Agent Occupancy AHT
05-Jan-15 Staff A 107 51.42 129
06-Jan-15 Staff A 77 49.58 167
07-Jan-15 Staff A 100 62.69 162
05-Jan-15 Staff B 68 61.99 198
06-Jan-15 Staff B 50 64.38 321
07-Jan-15 Staff B 67 74.51 255
05-Jan-15 Staff C 32 73.20 192
06-Jan-15 Staff C 20 59.11 194
07-Jan-15 Staff C 66 57.87 176
How can i lookup for Staff A, C=B & C Performance as below?
05-Jan-15 06-Jan-15 07-Jan-15
Productivity
Staff A =VLOOKUP("Staff A",Actuals,MATCH(05-Jan-15,Actuals,0),FALSE)
Staff B
Staff C
% Agent Occupancy
Staff A
Staff B
Staff C
AHT
Staff A
Staff B
Staff C
The formula seem doesn't work. May you assist?
Thanks,
Jenny
Instead of using lookup formula you can use Sumproduct function...
As per your info suppose you want to see the performance of Staff A for 5th Jan 2015...
So as per your column format the formula would be
Productivity
=SUMPRODUCT(--(TEXT($A$2:$A$11,"d-mmm-yy")="5-Jan-15")*($B$2:$B$11="Staff A")*($C$2:$C$11))
% Agent Occupancy
=SUMPRODUCT(--(TEXT($A$2:$A$11,"d-mmm-yy")="5-Jan-15")*($B$2:$B$11="Staff A")*($D$2:$D$11))
AHT
=SUMPRODUCT(--(TEXT($A$2:$A$11,"d-mmm-yy")="5-Jan-15")*($B$2:$B$11="Staff A")*($E$2:$E$11))
Please check...
Hi,
I have a series of data.
Category No. EBT 53,483
1 Computed expected tax 18,719
2 State taxes, net of federal effect 469
3 "Indefinitely invested earnings of
foreign subsidiaries" -4,744
4 Research and development credit, net -88
11 Domestic production activities deduction -495
11 Other 112
Provision for income taxes 13,973
I need vlookup to find the categories 11, add both the ocrresponsing numbers (i.e. -495 and 112) and present it in the cell.
Can i do this. I tried =SUM(VLOOKUP(lookup value, lookup range, {2,3}, FALSE)) but it doesnt work as the numbers are 1 blow the other.
Its urgent... can some1 plz help.
Thx
you can use the sumproduct formula...Suppose Column A contains Category number and columns B contains the corresponding number ...so to find the added value for category 11 you can use the below one...
=Sumproduct((A2:A12=11)*(B2:B12))
Please check and confirm...
imagine there are several numbers from 1 to N, and each number can be repeated n times. each number has a specific price. by entering a number from 1 to N How is it possible to get the lowest price from the data table. for example :
Numbers Price
1 300
3 400
7 700
8 650
6 300
5 200
1 150
3 400
7 210
7 340
Now by entering 7 we want to find the lowest price which is 210.
how is it possible?? please help me . thank you in advance.
Hi Alireza..
If you can sort the data by ascending order then I guess it would be possible by below method..
Suppose You have column from B2 to B11 the number from 1 to N and its corresponding column (C) contains the specific prices..Then sort the data from largest to smallest at column C then by Column B..After that use the below array formula ..
=(INDEX($C$2:$C$11,SMALL(IF(7=$B$2:$B$11,ROW($C$2:$C$11)-1,""),1)))
Please do not forget to press Cntl+Shift+enter after putting the formula..
Please let me know if it is working or not... In the mean time I will be looking for another method...
Thank you so much for your swift answer. I will try it and I will let you know.
HI Svetlana, i know..that its discussed here, but still i neeed some vlookup which would choose according to 2 criteria in one line /third value from another sheet/ and from this sheet will be filled into first sheet - /where are 2 criterias/
is any possible to send it the excel? that i could explain better. Thank you
Hi Svetlana, Thanks a ton for this article..
You've just nailed it Svetlana, love you! Many thanks for this Excel cheat lol
Help Full
Hello Svetlana;
How I can adjust age brackets with vlookup formula using multiple criterias.
Plan Gender Age Contribution
Plan A Male 0-17 1,703
Plan A Female 0-17 1,703
Plan B Male 0-17 1,569
Plan B Female 0-17 1,569
Plan C Male 0-17 1,426
Plan C Female 0-17 1,426
Plan A Male 18-30 1,260
Plan A Female 18-30 1,264
Plan B Male 18-30 979
Plan B Female 18-30 979
Plan C Male 18-30 2,597
Plan C Female 18-30 3,866
I have the date in above mentioned form and I want contribution data on an other sheet using vlookup with other 3 criterias for exact match on other sheet.
Hi Svetlana
When doing the formula =IFERROR(VLOOKUP($F$2,INDIRECT("$F$2,$B$2:$B$16,0)+2)&":$C16"),2,FALSE),"")to identify the 2nd occurrence for each name with the appropriate product, my formula returns a 0 every time I change the name. This is despite copying the one from the download sample and changing any cell references to match above. Would have any ideas as to why this happens?. Thank you.
how to use H lookup and V lookup for archiving data any suitable example?
Thank you for this interesting post. I could not find what I was looking for however.
I need to find values in rows with multiples criteria.
In the example below I have the same material being ordered under several PO numbers.
I need a formula that will tell me how many pieces of material #2 have been ordered under PO #2
Data table
Material# PO# OnOrder
material1 PO1 5
material2 PO1 10
material3 PO1 15
material2 PO2 10
material3 PO2 8
material4 PO2 12
Result table
PO2 (citeria 1)
material2 (criteria 2) result = 10
Thanks
Hi, I'm doing my best to understand vlookups. I wanted to know if you can further break down the following formula that you had posted. I want to fully understand why it works. The formula is from the tutorial about looking up duplicates with vlookup. Thank you:
{=INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))}
Hello,
I'm trying to add multiple VLOOKUP's together (week 1, 2, 3, etc), which I can do. But if one week is missing the item I'm looking up (person didn't take calls that week), it's giving me a "FALSE". I tried to use the ISERROR, but it keeps giving me a "0". I know why it gives the zero, but I'm not sure how to make it "skip" that week if the person isn't found.
Example:
IF(ISERROR((VLOOKUP(B2,'Team Stats Week 1'!B2:P21,2,FALSE))+(VLOOKUP(B2,'Team Stats Week 2'!B2:P21,2,FALSE))),0,(VLOOKUP(B2,'Team Stats Week 1'!B2:P21,2,FALSE))+(VLOOKUP(B2,'Team Stats Week 2'!B2:P21,2,FALSE)))
I am using google forms to make sure that staff read the circulars.
They fill up the form once they have read it.
The responses are automatically shown in a spreadsheet.
So, I have staff name in column B and Circular name in Column C
I want 0 to be shown in a column if a staff has read all 4 circulars.
How do I make that work?
Any help is greatly appreciated.
Hi Svetlana,
I want to fetch all items in comma separated in Sheet1, col(n) from Sheet 2 col(x) which look value is in col(x-2).
Please let me know how can i do this.
Thanks in advance.
Hi Svetlana,
Thank you so much! Your explanation was perfect. I understand now how the formula works and was able to successfully complete my project. Thank you very much for the quick response!
-Will
Hi Svetlana,
This is one of the best technical Excel posts I have ever had the pleasure to read. Thank you and well done! I do have a question however, what precisely are the "Row" functions doing in the array formula? I am having a hard time unpacking what is going on there... I understand the syntax but not the context. Would you please break that down a little bit more?
Lastly, in your example: "How to get all duplicate values in the lookup range", how would you re-write the formula to report the purchased products next to the customer name, starting in cell G2 and continuing on to the right to cell J2, etc?
Thank you for your help!
-Will
Hi Will,
Thank you for your kind words and a great question : )
I thought other readers might want to know the details too, so I've added the formula explanation to the post, hopefully it will be helpful.
And here's the formula "to report the purchased products next to the customer name, starting in cell G2 and continuing on to the right to cell J2":
=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=$B$2:$B$16, ROW($C$2:$C$16)-1,""), COLUMN()-6)),"")
i want to compare two spredsheet with name and amount in one sheet is contain in another sheet in same combination . wich formula i use
i want sheet to sheet multiply total formula .. excell sheet
Hi Prem,
It is difficult to recommend anything based on such generic description. Most likely you need to use the SUMPRODUCT function.
This is great! Thank you
CAN WE GET MORE THAN ONE COLOUMN OF DATA SIMULTANEOUSLY BY VLOOKUP...???
Hi, I have a workbook with multiple tabs. In my master sheet I have values in column A (AA, BB, CC, DD, EE....for example). Then I have tabs labeled AA, BB, CC, DD, EE. I'm trying to do the same vlookup but on different tabs depending on what my master sheet column A value has...
So if Column A is BB I want the vlookup to look at sheet BB. Here is the manual way of doing it:
=VLOOKUP(B1,BB!A:B,2,FALSE)
Here is what i want the formula mimic so it works in a similar fashion:
=VLOOKUP(B2,A2&"!A:B",2,FALSE)
I also tried giving A:B on sheet BB a reference of 'BB' in hopes this would work:
=VLOOKUP(B2,A2,2,FALSE)
Any help is appreciated!!!
I need to do the same sort of thing as Luke.
I Have a cell lets say its A1 that specifies from a drop-down list a Sheet Name (AA BB CC DD etc)
I want to pass that reference to a VLOOKUP Formula which would be SOMETHING LIKE =VLOOKUP(B5,A1&!D1:E22,2,False)
Where B5 is the cell containing theLookup_value, A1 is the cell containg the sheet name, and D1:E22 is the Table array.
I have been told the INDIRECT formula should work but I am jet to find a way to get a successful result.
Anyone Know?
I have the following
A 2
B 5
C 3
I want it to
A
A
B
B
B
B
B
C
C
C
Can you help me with excel formula
Hello Daro,
A special VBA script will be the best solution to your task. Sorry, I cannot help with this.
I need a formula were in can get what ever the data i feed in from sheet 1 to sheet 2 in the same workbook ?
Hello Samantha,
If you need to simply have the data copied from sheet1 to sheet2 as you enter it, you can put this formula in cell A1 on sheet2 and then copy it to all other cells:
=Sheet1!A1
Dear Sir,
Iam trying to do lookup with multiple cateria like
I have one sheet with account number,name & business
as well I have one sheet, when I will mentioned account number in sheet one formula will check aacount number & name then business will come
Hello Sandeep,
A similar example is described in How to do a vlookup with multiple criteria. This example explains how to look up with 2 criteria "Customer Name" and "Product". Your can download the example in the end of that section and adjust the cell references in the formulas.
Dear Svetlana,
I am very much impressed by the guidelines you are providing for the problems faced by the Excel users. Really it is a quite best Website for having expert advice for advanced Excel users.
Thanks for your sharing.
With regards.
CMA Anand
Thank you very much for your kind words, Anand! I'm pleased to know you've found the tutorial helpful.
Dear Svetlana,
thank you for such a great site. I'm actually learning to use excel as more than just a list keeper. As I was using your formula for duplicate values in a look up range, I ran into a few problems. The information was only being picked up in one cell (I'm searching for dates). After I set the dates in the original column and the lookup column to the exact format, all of the dates show except for the first dates on the original list. I'm sure it's a small tweaky thing but I've been at it for 3 hours with no success. Do you have any suggestions on what I should look for as I'm troubleshooting? Thank you.
Hi Svetlana,
Thanks for the suggestions - there are some very good ones in here. I am familiar with the VLOOKUP formula, but have often wondered if there is a way to look through several worksheets. I have a roster template I use and I create a new worksheet for each roster. I would like a worksheet at the frontthat acts like a summary table, where the first column contains a code, which matches the name of the relevant roster worksheet. The rest of the columns in the table will find the appropriate worksheet (based on what is in the first column) and return the data from each field in my roster template.
Any tricks you could suggest would be appreciated.
Thanks.
I need help with a formula. Can someone assist?
I believe this is the info i've been looking for to put together this project that i've been trying to wrap my head around
What i would like to do is Input a "part#" using VLOOKUP to list all of the tools I need to make said part (i've accomplished this much) at the same time have it kick back a number that refers to current inventory for said tools (this number can change at any moment). So i know i'm going to need more than 1 spreadsheet to complete this task. I can run an inventory report anytime and export it to excel so my question is, can i build a template that i can drop my current inventory list in to that already has the formulas written out? What are the formulas i need to use to tie the 2 spreadsheets together? We have hundreds of different part numbers and thousands of tools and not having a system in place has bogged us down. End goal is to avoid "spot buying" and get ahead of the curve and forecast my tool ordering before the job hits the floor. Tool list's with the part #'s are already populated awaiting my completion of this task
Thank you much and i hope theres no confusion here.
Hello Russ,
I am sorry, it is difficult to recommend anything without seeing your data. If you can send a sample workbook to support@ablebits.com and include the result you want to get, our support team will try to help.
EXAMPLE
Input:866637
Return: 1/4 drill 4pcs
.3438 drill 2pcs
3/4 end mill 1pc
1/2-14 npsf 5pcs
i figured one workbook would be my part numbers with tools listed and the 2nd being my current inventory.
DATE CODE SUBJECT NAME TIME
10-Nov-14 EC-601 English - IV FN
10-Nov-14 M-601 English –IV FN
10-Nov-14 EC-304 Communication AN
10-Nov-14 M-307 Machine Drawing AN
11-Nov-14 EC-404 Microprocessors FN
11-Nov-14 M-401 English-III FN
11-Nov-14 EC-104 Engineering AN
11-Nov-14 M-107 Engineering Drawing AN
I want only one item in Code column to be displayed one by one at different locations based DATE and TIME columns
Hello Aravind,
I am sorry, I cannot follow you. If you can send a sample workbook with your data to support@ablebits.com including the expected result, our support team will try to help.
Hi,
I need to get the PR number in Sheet: PO Short Table from Sheet : Pivot Table, based on Drawing ID and Material. Basically lookup & match 2 cells(A3,B3) and get the value from C3.
Which formula to use? Pls help.
Sheet : Pivot Table
Sum of PR Qty.
Drawing ID Material PR No. Total
LMV-41105060 SDSU16404121 3000053435 13
Sheet: PO Short Table
DWG ID System Matl No Matl Description PO Qty PR No
LMV-41105060 AFS SDSU16404121 STUD PAD 0
Hello Siva,
You need to look up by two columns, please see the example here:
https://www.ablebits.com/office-addins-blog/excel-index-match-function-vlookup/#lookup-multiple-criteria
I am creating a new sales analysis file to track sales over several years. My file has multiple worksheets. The first sheet, Sheet1, has raw data downloaded from our order entry system. Column D in Sheet1 holds the invoice date.
Because we run on a Fiscal year cycle, not a calendar year cycle, I now want to automatically calculate the correct fiscal year for the invoice date into Sheet1, Column F.
I have created a table on Sheet2 which holds date ranges that corresponds to fiscal years, ie:
Sheet2, Column A, Rows 1 thru' 10 = StartDate
Sheet2, Column B, Rows 1 thru' 10 = End Date
Sheet2, Column C, Rows 1 thru' 10 = Fiscal Year
I have been struggling to find a formula that will automatically calculate this information for me on Sheet1 in Column F. I have tried index-match, vlookup, lookup, LessThan, etc and cannot get this figured out.
Any help would be greatly appreciated. Thank you.
Hello Pam,
On Sheet 2, sort the dates by column A in ascending order, and then you can apply MATCH with the "less than" match_type parameter (1 or ommitted):
=INDEX(Sheet2!$B$1:$B$10,MATCH(D2, Sheet2!$A$1:$A$5,1))
Svetlana - Thank you for the reply. I will definitely give that a try today! Your help is greatly appreciated. (Sorry for my slow reply back - I have been out of the office this past week with the 'flu and just came back today).
I have one issue with my formula to find the value from another table on the same sheet that you can see their example as below.
Table A Table B
A U V
1 TC10001 RQ12345 TC10003,TC10002
2 TC10002 RQ12346 TC10003
3 TC10003 RQ12347 TC10002,TC10001
. . . .
. . . .
. . . .
100 TC10100 RQ12445 TC10050,TC10003,TC10001
My formula is "=VLOOKUP("*"&MID($A1,3,5)&"*",U:V,1,FALSE)". Can you please help me to correct my formula? My expected result is formula should return at least only one value of column V.
Thanks a lot in advance for any help
Beer :)
Thanks for this helpful article; I use vlookup with match formula that has solved my problem.
Thanks again Deen.
{=IFERROR(INDEX($B$2:$B$22,SMALL(IF($A$24=A2:A22,ROW(B2:B22)-1,""),ROW()-3)),"")}
This formula isn't working for me, after i press Shft+cntrl+enter nothing comes up.
Thanks, You're really very helpful
For Sure, I added your website to my favorite list and share to facebook
Hello,
I have two sheets with data.In third sheet, I would like to display the value of one cell by checking it in two sheets simultaneously. Can we do this with vlook up.
Ex: Name Sales Name Sales Name Sales
A 2 D 5 B ?
B 6 E 10 E ?
C 8 F 4 D ?
As you can see there are two different tables with different values. There is third table with same names. Now I want to show there sales by looking into both the tables.
What is the formula that I should be looking.
Thanks,
Avinash B
I would like to copy this formula down multiple rows on a spreadsheet:
=IF(ISERROR(INDEX($A$1:$B$8, SMALL(IF($A$1:$A$8=$E1, ROW($A$1:$A$8)), ROW(1:1)),2)),"", INDEX($A$1:$B$8, SMALL(IF($A$1:$A$8=$E1, ROW($A$1:$A$8)), ROW(1:1)),2))
But my index and therefore row # needs to change according to when the index number changes. Let's say I have 8 skus in my assortment. I know the first 5 will be new skus, so I would have my item number (1234, 455, etc.) filled in, but the sku column is blank. The last three are skus I'm carrying over and I don't need to worry about them. When I use the formula above, it works for the first three items because it refers back to cell A1 which is the first 1234. But when I get to 455, it leaves a blank cell because it's looking for the 4th row of A1. Is there anyway to have it move to the new number of 455 without having to retype in the new row number each time?
1234 this is new sku
1234 so is this
1234 and this
455 new sku1
455 new sku2
819 old sku1 - no formula
819 old sku2 - no formula
222 filled in - no formula
i have partial number like 456 out of full value 123456789(under id heading)
here table contains id,product,customer,q1,q2,a3
so how can i retrieve the full value(123456789) with the help of 456
please share your answers
Hello Svetlana,
Thanks for this tutorial. Your handling of Excel functions is too good.
I have a data of around 40000 rows of inter unit transfer entries and i have to get prices or data from other sales near to that date or before that date maximum 30 to 40 days.with the help of concatenate formula i made a key to get the data with the help of vlookup but the issue faced by me the vlookup function pick the value which it found first in the data but i have to get data near by to that date or of the the same date.I'm not understanding which formula we can use to extract data from the normal sales.Because the data is to huge it is very difficult to do this manually.IF it is possible please tell me the solution.
I am looking for a formula of vlookup in which we can use concatenate key of various columns and that is used on the basis of other date function like i have to select only that data that is nearby to that or below that date and the concatenated key may contain various similar entries but on different date.Have you any idea how can i do this.i want to this for transfer prices because it contains a huge data.
I am looking to do a lookup on based on two criteria, where one of the criteria would be based on a range rather than an exact match. Do you know how I can do that?
Here are the 2 criteria:
Lumen
Output Code
2170 32.F
4970 32.F
4971 42.CF
1185 15.CF
1407 32.F
1185 32.F
1086 32.F
The codes are an exact match, but the lumen output would fall between the following ranges, so for example, I'd want 2170 to return 18. I used the following formula to find a match based on the 2 criteria but it only works if it's exact: =LOOKUP(2,1/(B3:B13=C16)/(D3:D13=C18),(C3:C13))
Lumen
Output Wattage
1350 9
1800 12
1800 12
2100 14
2700 18
3300 22
3300 22
4970 28
5400 36
6600 44
Thank you in advance,
Andrea
Hello Andrea,
For Lumens, you can use the Match function with of "greater than" match_type (the 3rd argument is -1). So, first off sort your table from largest to smallest, and then use this formula:
=INDEX(B2:B11,MATCH(2170,A2:A11,-1))
Where A2:B11 is the table with your data.
You can find more information about the Index/Match functions in this article:
https://www.ablebits.com/office-addins-blog/excel-index-match-function-vlookup/
If you are looking for something different, you can send us your workbook at suport@ablebits.com and include the result you are looking for.
Thank you so much. And I can use this formula to match multiple criteria:
{=INDEX(C3:C13,MATCH(1,(B3:B13=C16)*(D3:D13=C18)*(E3:E13=C18),0))}
Hi,
I am really struggling on a work data base that I am trying to set up…
We start of with the Master Orders Workbook which consists of a summery sheet and store order sheets. We have to copy and past the order that come in onto this workbook.
I have then set up a Master POD’s workbook which pulls through all of the information put on to the Master Orders….but as this goes to our courier company they only need to see certain columns. This is all ok and working.
I am now stuck, as when the original orders come through there are 2 different warehouses on it that we pick from. WH1 in Singapore and WH2 in the UK my issue is that at the moment we have to manually go through all of the orders and split them into WH1 and WH2.
Is there a way of setting up a Master WH1 workbook were it will only pull this information through, even though the info I need it to look at is in column J and once it has found this I also need it to pull through the corresponding rows. I can’t provide the sum with an exact table as the size as the orders change for each order placed.
I am hoping that once I have sorted this out I will be able to do the same for WH2 and also cut my work load down by a lot!!
If you have any ideas it would be much appreciated.
Many thank, Fran
Hello Fran,
I am sorry, it is difficult to suggest anything without seeing your data. If you can send a sample workbook and the detailed description of workflow to support@ablebits.com, we'll try to help you.
Hi!
I'm trying to use your formula which I have put together as follow:
=IFERROR(INDEX(RFVDTL!$F$2:$F$1757, SMALL(IF($D$2=RFVDTL!A2:A1757,ROW(RFVDTL!F2:F1757)-1,""), ROW()-3)),"")
However, when I press Ctrl + Shift + Enter, I get an error s which is "Array Formulas are not valid in merged cell" can you help please?
Hi!
The point is that you are trying to apply an array formula to merged cells, which is not possible.
You can either unmerge the cells or enter an array formula into any other non-merged cell. If you choose the latter, then type =X20 in your merged cell, where X20 is the cell that contains an array formula.
I am trying to get all duplicate values in the lookup range, but I need help because some of the names on the left only contain part of the name. For example, I want to find all the part numbers for any "gold" material. So the names on the left could be:
Gold rock 123
Solid gold chair 234
Silver and gold frame 567
So could I put in "gold", and have it produce all three of those part numbers? instead of an exact match? I hope this makes sense.
Thanks
Hello Kat,
You need to add a helper column to your source table and enter the following formula there:
=IF(ISERR(SEARCH("gold",A2))=FALSE,"OK","")
Where A is the column with the original text.
Then in the master table, search for all "OK" instances in your helper column using formulas and pull out the corresponding Part numbers.