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 on: Advanced VLOOKUP in Excel: multiple, double, nested
by Svetlana Cheusheva, updated on
Comments page 9. Total comments: 540
greetings, i have a following query,
Dealers july aug sep
parts oil parts oil parts oil
A 100 50 80 30 70 40
B 120 45 115 50 125 55
C
i have a combined data of 3 dealers month wise with sub categories. i want to have a separate sheet which shows me target of a specific month. like if i ask July it shall show july targets of all dealers with sub categories.
How can I vlookup for each agent daily on another sheet to show lateness,absence and presence? Using data from response form submitted by each offline agents which appears in the format below.
Many thanks!
A1 = Check in time per date (9/12/2017 16:55:00, 9/13/2017 16:55:00....)
B C D E F G
Clock in (Names) MOD(A2,1) MOD(B2,1) Exceeds (Late15mins)
9/12/201716:58:01 Olalekan 4:55:00 PM 4:58:01 PM 00:03 Early
9/12/2017 16:58:08 Ikechukwu 4:55:00 PM 4:58:08 PM 00:03 Early
9/12/2017 16:58:29 Damilare 4:55:00 PM 4:58:29 PM 00:03 Early
9/12/2017 16:58:33 Abieu M 4:55:00 PM 4:58:33 PM 00:03 Early
9/12/2017 16:59:02 Ruth N 4:55:00 PM 4:59:02 PM 00:04 Early
9/12/2017 16:59:27 Anosike 4:55:00 PM 4:59:27 PM 00:04 Early
Hi
Is it possible to do a lookup for a narrative when the narrative differs slightly from tab to tab - so not an exact match.
Eg the narrative I want to look up is "Jimmy Choo 40ml EDT" but on another spreadsheet it is "Jimmy Choo 40ml EDT Spray".
Any help would be appreciated.
thanks
kandie
I need to lookup value of Product with No having latest date.
Product No Date
A 750000 14-09-2017
A 85101 15-09-2017
A 14413107 16-09-2017
B 41351 14-09-2017
B 1345654 15-09-2017
B 1531546 16-09-2017
Hello,
if you find the formula in the article above a bit complicated or you'd like to get a quicker and simpler solution, please take a look at our Vlookup Wizard add-in. You will find it in Ultimate Suite collection that can be downloaded from this web-page. The add-in can be used instead of VLOOKUP function and will return the value you need in a couple of clicks.
Hope it helps!
Hello
I have one problem that I can't solve it, I want to use multiple row in Vlookup, but i can't do it.
Example: IFERROR(VLOOKUP(A2:Z2,Data-sheet,2,0), "") the result can't, but if i use only one row IFERROR(VLOOKUP(A2,Data-sheet,2,0), "") it automatically show the result.
Please advice me because i need to do with multiple row.
Best,
How to apply the Vlookup or any other formula on long written statements.
These statements normally written in one cell.
Like a
On-line Banking bill payment to DHL Express Ref:-417930361
On-line Banking bill payment to TCS Express Ref:-417930361
I want to apply the Vlookup or any other formula on whole statement
Fore example if in side the statement there is word DHL, then its should write 1 otherwise, zero.
Please help for this issue or selecting the formula.
Thank You
Zubair
Hi, I have created a training report to pull the completion status of each of the trainee in my list. We do have several course translations so the challenge is that, I am not sure which among the course language translations did the trainee take to be marked "completed". I am taking the information from a learning management system's raw data but it contains 77k rows from multiple countries. I tried using a combination of nested IFERRORa and VLOOKUP functions to do this, and is using a helper column to combine the course name and the trainee's User ID then I added this in the first column of my massive raw data while the status comes next to it.
SAMPLE COURSE TRANSLATIONS
Cell X4: Course1 (English)
Cell X5: Course1 (Chinese)
Cell X6: Course1 (Simplified Chinese)
Cell X7: Course1 (ZH Chinese)
SAMPLE UNIQUE USERID
Cell A2: 123456
LookUP Table: WBT
My code looks like this;
=IFERROR(VLOOKUP(A2&" "&X4,WBT,2,FALSE),IFERROR(VLOOKUP(A2&" "&X5,WBT,2,FALSE),IFERROR(A2&" "&X6,WBT,2,FALSE),IFERROR(A2&" "&X7,WBT,2,FALSE),""))))
This formula does not return a value when for example the completed course is X5, X6, or X7. If the completed course is X5 and I put X5 in my first vlookup's lookup value, it returns the correct status. That means, it only runs the first vlookup formula.
One observation when I try running this formula below and when the completed course is X5,it doesn't return a #NA but just a blank cell. The 3rd, and 4th Vlookup when ran independently and the completed course is X5, returns #NA
=(VLOOKUP(A2&" "&X4,WBT,2,FALSE)
Any idea? i hope i was able to explain it correctly though. Thank you in advanced
Dear Svetlana,
May I know some clarity on this as mu trail was not successful.
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Copy the below formula to several adjacent cells, e.g. cells F4:F8 as demonstrated in the screenshot below. The number of cells where you copy the formula should be equal to or larger than the maximum number of possible duplicate entries. Also, remember to press Ctrl + Shift + Enter to enter an array formula correctly.
Getting all duplicate occurrences of the lookup value
Thanks much in advance.
Best
Shofikul
I have a qusition.
is there anyway to do vlook from a table to bring the value from a cells in different column and raw in the same time.
for example.
products factor Jan Feb March
A fcst 5 7 8
A actual 4 5 9
B fcst 10 9 15
B actual 12 8 14
I need to make a table to read only i.e. fcst for A products in the specific month.
is there anyway to do it specially when you have huge database.
I am trying to get a formula to work. I have a call log in which I would like to see if a voicemail that was left, was returned after the original voicemail was received. I came up with: =IF(A2="Sent to Voicemail",IF(VLOOKUP(B2,C3:C$7,1,FALSE),"Voicemail Returned","Voicemail not Returned"),"")
Column A = Disposition
Column B = ANI
Column C = DNIS
Column E = Voicemail Return (Formula Row)
DISPOSITION ANI DNIS Voicemail Return
Sent To Voicemail 4078675309 9876543210 Voicemail Returned
Follow up Call Attempt 9876543210 4078675309
Follow up Call Complete 5103359999 9876543210
Sent To Voicemail 5102999999 9876543210 #N/A
Caller Disconnected 9253009999 9876543210
Follow up Call Attempt 9876549999 9158509999
Any help would be greatly appreciated
Warren
Hi
Thank you so much for the article. It is really very helpful.
I am stuck at a point. I am trying to apply formula for duplicate entries. In your example, you have one customer name with multiple duplicate values. My sheet has got multiple customer names with duplicate values. I used the formula in the article, but the formula gives a nil value after 5 entries. Can you please help.?
Thanks
Prithi
Hi I came across this function
=Iferror(Proper(Concanate(VLook Up($A2,RIC,2,False),","(Vlook Up $A2,RIC,3FALSE))).
I want to know where RIC comes from
I wanna use the data in sheet 1 and get the simple output on the sheet 2 but the exact value in the corresponding label tab only. I have sent you the mail with the example workbook. looking for your reply here or on the email.
Thanks!!
maybe you can help me.
I have a table where someone enters data based on a part number, date, and units pulled.
I have a Vlookup formula on another sheet that totals the units pulled, based off part number.
What I want to do is modify the Vlookup formula to allow us to total the same data, but for a particular date range.
What i want to do is have an independent Cell on top at "A1" where we would enter the cut off date.
And then in the Vlookup formula for the tables add an if formula to the criteria that will let tell it to only pull data if the date in the date column
I know Vlookup is limited to One condition, and I may just be crazy, but this would be a huge help, otherwise I will be writing a bunch of If formulas pointing this all over the place.
How can I make this work?
Thank you.
i am giving the below format i an unable to understand how can use the vlookup formula in in this condition
I WANT THIS CUSTOMER CODE (1335) APR SALE AUTOMATIC IN APPEAR FY 15-16
EX-
SHEET 01
CUSTOMER CODE CUSTOMER NAME
1335 BALAJI SERVICE CENTRE
FY MONTH FY 15-16
Apr 0
May 0
Jun 0
Jul 0
Aug 0
Sep 0
Oct 0
Nov 0
Dec 0
Jan 0
Feb 0
Mar 0
SHEET02
Fiscal Month Cons Party Code Cons Party Name Cons Party Type Desc Net Retail Selling
Apr TUA0205285 AMOL AUTOMOBILES TRADER/RETAILER -687.2
Apr TUA0203882 SIMRAN AUTO TRADER/RETAILER 3,256.4
Apr TUA0205283 AJEET MOTORS TRADER/RETAILER 845.8
Apr TUA0205284 ALMORA AUTO CARE TRADER/RETAILER 30,212.4
Apr TUA0205285 AMOL AUTOMOBILES TRADER/RETAILER 24,195.8
Apr TUA0205286 ARORA AUTO SPARES TRADER/RETAILER 58,845.3
Apr TUA0205289 BAJRANG AUTOMOBILES TRADER/RETAILER 7,341.0
Apr TUA0205290 BALBEER MOTORS TRADER/RETAILER 91,719.8
Apr TUA0205292 BANSAL TRACTORS TRADER/RETAILER 38,561.1
Apr TUA0205293 BATRA MOTORS TRADER/RETAILER 12,619.4
Hi if I wana do an vlookup with an condition stating some specific word if that reflect the beside it only then the value in the column should be captured can someone help me with the it...???
for example
for 2 if satnam is present then the value should appear in from of two only specific value "Satnam" Note
2-Satnam 12345 2
3-Kiv 4567
4-New 9756
Hi if I wana do an vlookup with an condition stating some specific word if that reflect the beside it only then the value in the column should be captured can someone help me with the it...???
for example
for 2 serial number if satnam is present then the value should appear in front of two only specific value "Satnam"
2-Satnam 12345
3-Kiv 4567
4-New 9756
2
i want to split qty of single cell.
Input
Sheet -1
Material PO qty Req. Qty
XX1 1 300
XX1 2 200
XX1 3 200
XX1 4 350
XX1 5 500
XX1 6 200
XX1 7 200
XX1 8 200
Material Batch Available qty
XX1 a 100
XX1 b 100
XX1 c 100
XX1 d 750
XX1 e 250
XX1 f 250
XX1 g 600
Req. Output as below
PO qty Req. Qty Batch Available qty Material remarks
1 100 a 100 XX1 PO qty Spilt
1 100 b 100 XX1 PO qty Spilt
1 100 c 100 XX1 PO qty Spilt
2 200 d 200 XX1 Batch qty Spilt
3 500 d 200 XX1 Batch qty Spilt
4 200 d 350 XX1 Batch qty Spilt
5 250 e 250 XX1 PO qty Spilt
5 250 f 250 XX1 PO qty Spilt
6 200 g 200 XX1 Batch qty Spilt
7 200 g 200 XX1 Batch qty Spilt
8 200 g 200 XX1 Batch qty Spilt
Hi,
I request guidance to solve below..
Need to compare one cell value with below table (A to P) and map 5th column value..
for eg Y = 33
C=22
P=44
A B C D 22
X Y Z K 33
M N O P 44
Lookup tables are working for either row wise or column wise,
please support..
Hi,
I am having an issue with building a report. I am trying to identify Precinct information by a street number range. I am attempting to do so by using a Vlookup to find the street (working).With the numbers by if statements to say that 17 A St falls within the A St 1 to 20 range 1 being in cell b2 and 20 being in cell c2.
Hi,
Thanks for creating this site. Definetely the most detailed, practical and useful information.
In the section "How to use VLOOKUP to get 2nd, 3rd, 4th, etc. matching values", I understood the way to find the 2nd occurence. But, could you also share the way to get the 3rd or nth occurence?
HI,
I am trying to easily portray an inventory availability list that instead of using style and size codes, simply indicates if a a style has the sizes in stock.
For example. Item: MD195, has sizes XS, S, M, L, XL. however, each size has a specific #, XS = MD195-01-31, S = MD195-01-32, etc.
I am trying to get it to update every time i load the current inventory, if it is available in XS, put yes or no, Small yes or no, etc.
The real difficulty comes when I try to drag the formula to apply to all cells, while referencing the appropriate cell(s).
I applied vlookup multiple rows with the same lookup value with index farmulla, my same lookup values are more than 3 i.e. if it if 10, then it shows only 1st value as a result and then #num error. rest of 9 values are not shows. i copied the farmulla 10 times... but results not came.
pls help me with this erros
Excel VBA VLOOKUP successfully returns matching data AND also returns an Err.Number = 1004. The datum are dates. I am suspecting the data types as being the issue. Since the VLOOKUP is successful it does not present a problem but, it is puzzling and I would like to be certain there is not underlying issues.
Why would a successful VLOOKUP throw a false error?
Hi, i need ur assistance to get the result using formulas especially using vlookup. Example i have a large data. In a column i have a account numbers with different branches. Also few account numbers are same but different branches. Is it possible to get the result using vlookup with same account number with different branches from the large data. Please help me...
And i would like to say thank you for providing the detailed functions of formulas. It is really helpful for me.
Use
=vlookup(a2&b2,data,3,false)
Sara, please read the section above entitled "How to do two-way lookup in Excel". I think this is what you need to implement.
Sara, I think your problem would be solved using more of a 2 dimensional vlookup:
https://www.ablebits.com/office-addins-blog/index-match-match-two-dimensional-lookup-excel/
Hello,
Try to write a formula with if and vlookup and I think I got it to work but I'm having trouble making to work for a entire year I need.
Example:
Sheet 1
October November
Names Hours Hours
John Smith 25 15
Katie Wilson 45 55
Jeremy Pate 37 20
Sheet 2
October
Hours
Names
John Smith
Katie Wilson
Jeremy Pate
=IF($B$1='Sheet1'!$B$1,VLOOKUP(A1,'Sheet1'!A:K,2,0),"no")
With this formula if B1(October) in sheet1 than it matches and the formula shows me the hours. However for November unless I change the formula I can't get the hours to appear in Sheet 2. Sheet 2 has ONLY one column for hours and I can't add anymore due to reporting restrictions. How do I get excel to automatically update it self if change "B1" Cell from October to November.
Please help. Thank you
plz clarify following senario,
Table _ 01
Compay No of Employees
aaa 221
aab 245
aac 250
aad 290
aae 271
aaf 340
aag 193
aah 213
aai 90
Table _ 02
Compay No of Employees
aaa 196
aai 345
aab 650
aag 193
aae 278
Expected result
Table _ 03
Compay No of Employees
aaa 196
aab 650
aac 650
aad 290
aae 278
aaf 340
aag 193
aah 213
aai 345
I used formula =VLOOKUP(D4,A$17:B$21,2,0) and ends up with following results
Table _ 04
Compay No of Employees
aaa 196
aab 650
aac #N/A
aad #N/A
aae 278
aaf #N/A
aag 193
aah #N/A
aai 345
how can i obtain correct results
NB:
i need to update table 01 referring table 02 and if data entry is not available in table 02 the data entry in table 01 should be remain as it is.
appreciate your kind response thanks and regard
In your section “How to get all duplicate values in the lookup range” you explain how to use Index, Small and Row to generate the list of values. I think there is a simpler way. Try the following. Select the range F4:F10 or a range with enough rows to hold the maximum number of expected values. Enter the following in the formula bar and then press SHIFT-CTRL-ENTER
=IF(B2:B16=F2,C2:C16,"")
I hope this works. I would like some help too however. I would like to make the list created by the above array formula to appear in the dropdown box for the allowed values for another cell i.e. using the Excel Data Validation functionality. However, I cannot get this to work. It seems as if array functions cannot be entered into the Data Validation entry box. Do you have any suggestions?
Ian
I'm having difficulty getting one of your examples to fit my scenario. I have 2 tables. Each includes the person's name and a date. I want to match the name / date combination in one table with the same combination in the other table, then return a third field.
In one table, I have dates across the top (column headings) and names down the side (row headings). I want to match the date / name combination from that table to the date / name combination in another table (see below) and return another field, in this case the city.
10/9/16 Bob Boston
10/16/16 Phil Philadelphia
11/13/16 Pete New York
Any help would be appreciated.
Thank You.
Thank you so much for this useful tips. Really reducing my work by multiple folds.
Regards,
Aravind
HI I have a 3 page workbook I have built and on all is number to be referenced in column A sheets 1 - 3. I have been sent another workbook that has the same numbers in column A. I need to in column B on the one sent in to reference the number in column A and locate that single occurrence of that number on My 3 pager and input the corresponding data from each column N on my 3 pager. I assume I will need 3 Vlookups but how do I lay that out in the Cell B on the one sent ?
Hi
I'm trying to get all duplicate values in the lookup range from pivot table report into manual customised report.
1. Pivot table worksheet have multiple duplicate names in the first column with various values in the eighth column.
2. Summary worksheet have manual customised report layout with merged rows of all the names which are in the pivot table report.
3.I would like to display all the values of those duplicate names from the pivot table to the manual summary worksheet report.
Is that possible, please give me the right formula.
I tried {=IFERROR(INDEX($K$7:$K$60,SMALL(IF($P$5=D7:D60,ROW(K7:K60),""),ROW()-3)),"")}, but it shows no values.
please help.
thanks in advance
Try This ROWS($E15:E15)),2) instead of this ROW()-3)),"")
($E15:E15)) is a resulting value, where you want your result,,,
If your answer value is in 2nd coloumn then type 2, if it is in 4th coloum then type 4 and close bracket
How to use VLOOKUP to get maximum value
i have 2 worksheets
sheet1( call it a data sheet)
columnA columnB
name marks
robert 2
robert 3
robert 4
charles 2
charles 5
charles 6
i want to write a vlookup or a suitable formula in sheet2 which will pull max marks from sheet1 and get the results
for e.g.if i type robert it will dispaly 4 as the answer( maximum value )
sheet2 ( formula sheet)
columnA columnB
name max_marks
robert 4
charles 6
any help!
How to apply double vlookup in single cell.is there any possibility.
Good Lesson
Hi,
I have a 2 column table (Wards and teams) to know what team does roadworks in what ward.
Some roadworks are in multiple wards however, needing several teams.
In this event, the column 'wards' in another table (called roadworks) would show each ward separated with a '/'. Some ward names have spaces in them.
How would I do a Vlookup that shows me all the teams that should be contacted for a roadworks' job?
No response needed ... this is just a complement.
"This web site is remarkable!!! I had always wondered how to go about solving some of the more complex problems I face at work; I can now say that I can, thanks to this web site. Thank you so much !!!! Alex."
hi,
if i want the formula return in column instead of row
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Instead of images, if you can use actual tables for your examples, we can copy and experiment in our Excel environment. Thank you!
I've been working on a 2 criteria VLookup and have it working, but I need to know if there's a way to display the found data in the same format as it exists in the source table.
The columns I'm pulling data from may contain "$ values", or "% values".
(Ex: Where 'Name=Neal' and 'Month=Mar')
Name Jan Feb Mar Apr May Jun
Pete $26 $34 $33 $37 $41 $44
Sally $41 $29 $0 $49 $24 $34
Neal $42 $36 100% $38 $25 $37
Diana $40 $18 0% $38 $35 $32
I've tried using conditional formatting on the 'results' fields, to change the format based on value, but I can't figure out when "'$0' vs '0%'", 100% vs $100, etc.
I thought I could expand my vlookup to include the Cell() function to return the formatting, but don't know if this is the nest way.
Any thoughts?
if i have data like
code code no.
1001 2001 1
1002 3001 2
1003 1001 3
i want to lookup 1001 in different work book & no. infron of code
kindly help which formula to use
very useful info you have there. thank you very much. but i could not make the formula work. can you please help me? i use vlookup for a specific text(column A) and data next cell (column B, col_index_num)) but when the text is in more than one cell, i need the different datas concatenated with space. Can you please type the formula?
Hi There
I you can help. I am trying to get the below formula to work. It is bringing me back the Case values but N/A for anything else.
Essentially I have a code in a cell and I want to know which column this code corresponds from three columns of codes on another sheet.
This is the formula I have tried. I have tries nesting IF statements combined with vlookup statements. If at all possible I would like to have this all in one cell rather than spread out. Any help would be much appreciated.
=IF(A2=VLOOKUP(A2,Compare!H:H,1,FALSE),"Case",IF(A2=VLOOKUP(Sheet1!A2,Compare!G:G,1,FALSE),"Shrink","Item"))
I have also tried
=IF(VLOOKUP(A2,Compare!H:H,1,FALSE)=A2,"Case",IF(VLOOKUP(Sheet1!A2,Compare!G:G,1,FALSE)=A2,"Shrink","Item"))
Dear sir,
If you please help me to solve the problem.
I have
(Sheet 1)
A=1
B=2
C=2
A=4
B=5
C=6
I want(Sheet 2)
Sum of A=?
B=?
C=?
I Will be highly oblized to you.
Regards
Pappu Pattanayak
Hi Pappu,
You can use below simple command
Think its in one sheet and result is also published in one sheet
=SUMIFS($B$:$B$,$A$:$A$,D1)
Can anybody help me with this:
example:
Col.A Column B
States Sales Rep Name
AZ, CA Blank
WA,ID,OR Blank
TX, OK, Blank
in column A, there's abbreviation of states, like CA, NV, AZ etc...
I need to conduct a Vlookup that searches the range of these states in (A) and cross references the master Table (on another sheet) to populate Column B. My challenge is that i can't find a Vlookup to search the range of 50 states. Any suggestions?
thank you
Hello,
I have sales for about 1000 customers split by various services all grouped into one invoice by split by line item. I need to pull this information and insert the values of each service by customer in a table.
Source:
Client 1 Plumbing done on 11th $500
Client 1 Plumbing done on 24th $500
Client 1 Work done for Carpentry $1000
Client 2 Plumbing was done on 25th $1000
Client 2 Installation done on 10th $250
Client 2 Carpentry done on 10th $700
Client 2 Carpentry done on 10th $1700
I need this info in the below table format (Note, I need sum of each service for each client)
Clients, Plumbing, Carpentry,Installation
Client 1 $1000 $1000 0
Client 2 $1000 $2400 $250
The above information looks simple, but the description of each item is a sentence out of which I need to pick a "code word" to identify the service. Thank you so much for your help!
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
Working with this formula, but getting the #N/A error.
Material Material Description 8/1 7/25 7/18
105939 EBF Org Wild Arugula 2x2lb PL 574 1,350 21,321
105940 EBF Org Baby Spin - 2x2lb PL 1,741 1,345 1,564
2,315 2,695 22,885
Need to lookup a material (105940) and bring the qty. on a specific date (7/18). Does this not work because it's trying to reference dates?
I have a excel file where I daily populate cash transactions for several bank accounts. I need to a daily rollfoward with beginning to eOD balances. I prepared a tab for each account and the date in column A and columns B thru J have the cash category. I performed a pivot table that has Account,date,category and total. I need to find a way to drop the data from each bank account into each tab I made so the rollforward can be updated by a simple refresh. I think this can be done using complex if then and combine Hlookup tables but I am not able to drop each dollar amount in its corresponding category by date
Dear Sir,
I am using Microsoft excel 2007 I want to use vlookup formulla with 3 lacs column but it could not work, it shows error message "invalid reference and this file verson can not contain formulla that reference cell beyond a worksheet size of 236 column (column IW of higher) or 65536 rows".
I don't understand please help me what I do??????????