by These examples will teach you how to Vlookup multiple criteria, return a specific instance or all matches, do dynamic Vlookup in multiple sheets, and more.
It is the second part of the series that will help you harness the power of Excel VLOOKUP. The examples imply that you know how this function works. If not, it stands to reason to start with the basic uses of VLOOKUP in Excel.
Before moving further, let me briefly remind you the syntax:
Now that everyone is on the same page, let's take a closer look at the advanced VLOOKUP formula examples:
How to Vlookup multiple criteria
The Excel VLOOKUP function is really helpful when it comes to searching across a database for a certain value. However, it lacks an important feature - its syntax allows for just one lookup value. But what if you want to look up with several conditions? There are a few different solutions for you to choose from.
Formula 1. VLOOKUP with two criteria
Suppose you have a list of orders and want to find the quantity based on 2 criteria, Customer name and Product. A complicating factor is that each customer ordered multiple products, as shown in the table below:
A usual VLOOKUP formula won't work in this situation because it returns the first found match based on a single lookup value that you specify.
To overcome this, you can add a helper column and concatenate the values from two lookup columns (Customer and Product) there. It is important that the helper column should be the leftmost column in the table array because it's where Excel VLOOKUP always searches for the lookup value.
So, add a column to the left of your table and copy the below formula across that column. This will populate the helper column with the values from columns B and C (the space character is concatenated in between for better readability):
=B2&" "&C2
And then, use a standard VLOOKUP formula and place both criteria in the lookup_value argument, separated with a space:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Or, input the criteria in separate cells (G1 and G2 in our case) and concatenate those cells:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
As we want to return a value from column D, which is fourth in the table array, we use 4 for col_index_num. The range_lookup argument is set to FALSE to Vlookup an exact match. The screenshot below shows the result:
In case your lookup table is in another sheet, include the sheet's name in your VLOOKUP formula. For example:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Alternatively, create a named range for the lookup table (say, Orders) to make the formula easier-to-read:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
For more information, please see How to Vlookup from another sheet in Excel.
Note. For the formula to work correctly, the values in the helper column should be concatenated exactly the same way as in the lookup_value argument. For example, we used a space character to separate the criteria in both the helper column (B2&" "&C2) and VLOOKUP formula (G1&" "&G2).
Formula 2. Excel VLOOKUP with multiple conditions
In theory, you can use the above approach to Vlookup more than two criteria. However, there are a couple of caveats. Firstly, a lookup value is limited to 255 characters, and secondly, the worksheet's design may not allow adding a helper column.
Luckily, Microsoft Excel often provides more than one way to do the same thing. To Vlookup multiple criteria, you can use either an INDEX MATCH combination or the XLOOKUP function recently introduced in Office 365.
For example, to look up based on 3 different values (Date, Customer name and Product), use one of the following formulas:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Where:
- G1 is criteria 1 (date)
- G2 is criteria 2 (customer name)
- G3 is criteria 3 (product)
- A2:A11 is lookup range 1 (dates)
- B2:B11 is lookup range 2 (customer names)
- C2:C11 is lookup range 3 (products)
- D2:D11 is the return range (quantity)
Note. In all versions except Excel 365, INDEX MATCH should be entered as an CSE array formula by pressing Ctrl + Shift + Enter. In Excel 365 that supports dynamic arrays it also works as a regular formula.
For the detailed explanation of the formulas, please see:
How to use VLOOKUP to get 2nd, 3rd or nth match
As you already know, Excel VLOOKUP can fetch only one matching value, more precisely, it returns the first found match. But what if there are several matches in your lookup array and you want to get the 2nd or 3rd instance? The task sounds quite intricate, but the solution does exist!
Formula 1. Vlookup Nth instance
Suppose you have customer names in one column, the products they purchased in another, and you are looking to find the 2nd or 3rd product bought by a given customer.
The simplest way is to add a helper column to the left of the table like we did in the first example. But this time, we will populate it with customer names and occurrence numbers like "John Doe1", "John Doe2", etc.
To get the occurrence, use the COUNTIF function with a mixed range reference (the first reference is absolute and the second is relative like $B$2:B2). Since the relative reference changes based on a position of the cell where the formula is copied, in row 3 it will become $B$2:B3, in row 4 - $B$2:B4, and so on.
Concatenated with the customer name (B2), the formula takes this form:
=B2&COUNTIF($B$2:B2, B2)
The above formula goes to A2, and then you copy it down to as many cells as needed.
After that, input the target name and occurrence number in separate cells (F1 and F2), and use the below formula to Vlookup a specific occurrence:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Formula 2. Vlookup 2nd occurrence
If you are looking for the 2nd instance of the lookup value, then you can do without the helper column. Instead, create the table array dynamically by using the INDIRECT function together with MATCH:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Where:
- E1 is the lookup value
- A2:A11 is the lookup range
- B11 is the last (bottom-right) cell of the lookup table
Please note that the above formula is written for a specific case where data cells in the lookup table begin in row 2. If your table is somewhere in the middle of the sheet, use this universal formula, where A1 is the top-left cell of the lookup table containing a column header:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
How this formula works
Here is the key part of the formula that creates a dynamic vlookup range:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
The MATCH function configured for exact match (0 in the last argument) compares the target name (E1) against the list of names (A2:A11) and returns the position of the first found match, which is 3 in our case. This number is going to be used as the starting row coordinate for the vlookup range, so we add 2 to it (+1 to exclude the first instance and +1 to exclude row 1 with the column headers). Alternatively, you can use 1+ROW(A1) to calculate the necessary adjustment automatically based on the position of the header row (A1 in our case).
As the result, we get the following text string, which INDIRECT converts to a range reference:
INDIRECT("A"&5&":B11") -> A5:B11
This range goes to the table_array argument of VLOOKUP forcing it to start searching in row 5, leaving out the first instance of the lookup value:
VLOOKUP(E1, A5:B11, 2, FALSE)
How to Vlookup and return multiple values in Excel
The Excel VLOOKUP function is designed to return just one match. Is there a way to Vlookup multiple instances? Yes, there is, though not an easy one. This requires a combined use of several functions such as INDEX, SMALL and ROW is an array formula.
For example, the below can find all occurrences of the lookup value F2 in the lookup range B2:B16 and return multiple matches from column C:
{=IFERROR(INDEX($C$2:$C$11, SMALL(IF($F$1=$B$2:$B$11, ROW($C$2:$C$11)-1,""), ROW()-1)),"")}
There are 2 ways to enter the formula in your worksheet:
- Type the formula in the first cell, press Ctrl + Shift + Enter, and then drag it down to a few more cells.
- Select several adjacent cells in a single column (F1:F11 in the screenshot below), type the formula and press Ctrl + Shift + Enter to complete it.
Either way, the number of cells in which you enter the formula should be equal to or larger than the maximum number of possible matches.
For the detailed explanation of the formula logic and more examples, please see How to VLOOKUP multiple values in Excel.
How to Vlookup in rows and columns (two-way lookup)
Two-way lookup (aka matrix lookup or 2-dimentional lookup) is a fancy word for looking up a value at the intersection of a certain row and column. There are a few different ways to do two-dimensional lookup in Excel, but since the focus of this tutorial is on the VLOOKUP function, we will naturally use it.
For this example, we'll take the below table with monthly sales and work out a VLOOKUP formula to retrieve the sales figure for a specific item in a given month.
With item names in A2:A9, month names in B1:F1, the target item in I1 and the target month in I2, the formula goes as follows:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
How this formula works
The core of the formula is the standard VLOOKUP function that searches for an exact match to the lookup value in I1. But since we do not know in which exactly column the sales for a specific month are, we cannot supply the column number directly to the col_index_num argument. To find that column, we use the following MATCH function:
MATCH(I2, A1:F1, 0)
Translated into English, the formula says: look up the I2 value in A1:F1 and return its relative position in the array. By supplying 0 to the 3rd argument, you instruct MATCH to find the value exactly equal to the lookup value (it's like using FALSE for the range_lookup argument of VLOOKUP).
Since Mar is in the 4th column in the lookup array, the MATCH function returns 4, which goes directly to the col_index_num argument of VLOOKUP:
VLOOKUP(I1, A2:F9, 4, FALSE)
Please pay attention that although the month names start in column B, we use A1:I1 for the lookup array. This is done in order for the number returned by MATCH to correspond to the column's position in table_array of VLOOKUP.
To learn more ways to perform matrix lookup in Excel, please see INDEX MATCH MATCH and other formulas for 2-dimensional lookup.
How to do multiple Vlookup in Excel (nested Vlookup)
Sometimes it may happen that your main table and lookup table do not have a single column in common, which prevents you from doing a Vlookup between two tables. However, there exists another table, which does not contain the information you are looking for but has one common column with the main table and another common column with the lookup table.
In below image illustrates the situation:
The goal is to copy prices to the main table based on Item IDs. The problem is that the table containing prices does not have the Item IDs, meaning we will have to do two Vlookups in one formula.
For the sake of convenience, let's create a couple of named ranges first:
- Lookup table 1 is named Products (D3:E10)
- Lookup table 2 is named Prices (G3:H10)
The tables can be in the same or different worksheets.
And now, we will perform the so-called double Vlookup, aka nested Vlookup.
First, make a VLOOKUP formula to find the product name in the Lookup table 1 (named Products) based on the item id (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Next, put the above formula in the lookup_value argument of another VLOOKUP function to pull prices from Lookup table 2 (named Prices) based on the product name returned by the nested VLOOKUP:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
The screenshot below shows our nested Vlookup formula in action:
How to Vlookup multiple sheets dynamically
Sometimes, you may have data in the same format split over several worksheets. And your aim is to pull data from a specific sheet depending on the key value in a given cell.
This may be easier to understand from an example. Let's say, you have a few regional sales reports in the same format, and you are looking to get the sales figures for a specific product in certain regions:
Like in the previous example, we start with defining a few names:
- Range A2:B5 in CA sheet is named CA_Sales.
- Range A2:B5 in FL sheet is named FL_Sales.
- Range A2:B5 in KS sheet is named KS_Sales.
As you can see, all the named ranges have a common part (Sales) and unique parts (CA, FL, KS). Please be sure to name your ranges in a similar manner as it's essential for the formula we are going to build.
Formula 1. INDIRECT VLOOKUP to dynamically pull data from different sheets
If your task is to retrieve data from multiple sheets, a VLOOKUP INDIRECT formula is the best solution – compact and easy-to-understand.
For this example, we organize the summary table in this way:
- Input the products of interest in A2 and A3. Those are our lookup values.
- Enter the unique parts of the named ranges in B1, C1 and D1.
And now, we concatenate the cell containing the unique part (B1) with the common part ("_Sales"), and feed the resulting string to INDIRECT:
INDIRECT(B$1&"_Sales")
The INDIRECT function transforms the string into a name that Excel can understand, and you put it in the table_array argument of VLOOKUP:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
The above formula goes to B2, and then you copy it down and to the right.
Please pay attention that, in the lookup value ($A2), we've locked the column coordinate with absolute cell reference so that the column remains fixed when the formula is copied to the right. In the B$1 reference, we locked the row because we want the column coordinate to change and supply an appropriate name part to INDIRECT depending on the column into which the formula is copied:
If your main table is organized differently, the lookup values in a row and unique parts of the range names in a column, then you should lock the row coordinate in the lookup value (B$1) and the column coordinate in the name parts ($A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Formula 2. VLOOKUP and nested IFs to look up multiple sheets
In situation when you have just two or three lookup sheets, you can use a fairly simple VLOOKUP formula with nested IF functions to select the correct sheet based on the key value in a particular cell:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Where $A2 is the lookup value (item name) and B$1 is the key value (state):
In this case, you do not necessarily need to define names and can use external references to refer to another sheet or workbook.
For more formula examples, please see How to VLOOKUP across multiple sheets in Excel.
That's how to use VLOOKUP in Excel. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Advanced VLOOKUP formula examples (.xlsx file)
540 comments
I have an Excel file with multiple columns. I'm trying to do multiple vlookups in one cell to check each column starting with column A for the SkU, column B and the next few columns up to G to return the total depth for that row (part number in column A) that is reflected In column H. All the part numbers in each column is linked to the part in column A, which are alternate part numbers. Any help anyone can offer will be greatly appreciated. I can send you my excel file, too.
Hello!
To do multiple-column searches, use a nested VLOOKUP as described in the article above. If you want to find all values in a range, try this instruction: Vlookup to return multiple results in one cell (comma or otherwise separated). I'd recommend you to have a look at our Vlookup Wizard. This tool is part of Ablebits Ultimate Suite that includes 70+ professional tools and 300+ solutions. You can install it in a trial mode and check how it works for free.
I'm trying to use xlookup with multiple criteria across several columns and ~15000 rows of data. The xlookup function returns a value for each row, but the data matches the return array row and not the criteria across columns. For example, data in row 100 in both my table and the return array (source) file is the same, even though the criteria is from row 90 (I don't need all 15,000 rows of data). Do you know why the formula is picking up the data in the row and not from the criteria related to the row?
Hi!
The XLOOKUP formula selects the data that you have specified in it.
In a MASTER sheet, I'm having SKU, fulfillment center, and Quantity. need to fetch quantity according to the matching of SKU and fulfillment center in another sheet. because the data of the master sheet will change every time.
Hi!
You can learn more about VLOOKUP with multiple criteria in this article above. If this is not what you wanted, please describe the problem in more detail.
Hello,
I am trying to put some data from baseball box scores into an Excel sheet. What I am trying to do specifically is bring in the pitchers for each team into a section of the sheet and then populate another area if a pitcher gets a certain stat (Win, Loss, Hold, Blown Save and Save).
Each game will have a pitcher get a Win or a Loss but the other three stats may or may not happen each game. The pitcher will be listed with a First Name and Last Name unless they get the certain stat and then the stat will be there along with either how many of the stat or their win-loss record. (Examples: John Smith or John Smith, W (4-3) or John Smith, L (3-4) or John Smith, H (17) or John Smith, BS (3) or John Smith, S (10))
Here is an example:
I put the stats in column A1:A5 (W L H BS S) as the lookup value for the stats.
The pitchers will be copied in column C - Visiting Pitchers in C1:C8 and Home Pitchers in C10:C17. These cells may not all be filled in each game.
What I want to do is look in both columns and find the stat looked up in column A and put the pitcher's name, stat and/or record from the examples I gave above in the cell that applies to the stat. So I want John Smith W (4-3) from the list to go in cell D1 for example for the winning pitcher. The cell for Win and Loss will each only have one result as well as Save. Hold and Blown Save can have more than one result and I can lost those in multiple cells.
I hope I have explained this well enough and I can provide more clarity if necessary.
Thanks for any help you can provide.
Hi!
This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.
Scenario:
Sheet 1 is having Names in Column 1 and Row1 is having the Dates.
Sheet 2 is having Names in Column 1 and Row1 is having Dates.
I need the formula to return the Dynamic index (Cell).
Ex: Sheet 1 If the Name and Date match with the Sheet 2 Name and date - Return the particular column values.
Hi!
Pay attention to the following paragraph of the article above – How to Vlookup in rows and columns (two-way lookup).
It covers your case completely.
Please help!! Been using Vlookup for a year already the same data over and over and got no N/A nor errors, but this fast weeks we've been experiencing NA. Absolutely sure that formula is correct, lookup_value and table_array references were made absolute correct. Still looking for what may have caused the N/A then correct data, the N/A again cycle goes on like this below: Thank you
#N/A
#N/A
MARIES
Karmelyn
Liza
Ely
Lara
#N/A
#N/A
#N/A
#N/A
Hi!
Please have a look at this article: Excel VLOOKUP not working - solving #N/A, #NAME, #VALUE errors.
First of all, pay attention to the leading and trailing spaces in your values.
HI!
I am struggling with a VLOOKUP and Im not sure why
I have a column of 18 fields C2:C18
Coulmn A is filled with roughly 3000+ fields, some of which match what is in the range C2:C18
Column B is filled with account numbers
How do I lookup the C2:C18 in column A and return the account number from column B that has a match?
Hello!
If I understand your task correctly, try the following formula:
=VLOOKUP(C2,A2:B3000,2,FALSE)
I recommend reading this guide: Excel VLOOKUP tutorial for beginners.
Hey!
I am looking to see if any of those 18 values from column C match anything in Column A and if they do to return the account number from Column B.
There might be multiple matches in Column C but a different account number that matches from B
That formula would only check column A for 1 of the 18 from Column C?
Hi!
I don't want to guess what you're looking for. Give an example of the source data and the expected result.
Charge (A) Acc number (B) Charge to look for (C)
XXX1234 Acc 1 CCC1234
AAA1234 Acc 2 PPP1234
BBB1234 Acc 3 SSS1234
CCC1234 Acc 4 EEE1234
DDD1234 Acc 5
EEE1234 Acc 6
FFF1234
GGG1234
HHH1234
III1234
So I am looking to search the entire of Column A for any result that matches the entire of Column C and return the account number from Column B that the charge is on. I hope that makes more sense, apologies!
=VLOOKUP(C2:C18,$A$2:$B$3111,2,0)
This is what I came up with but I'm getting an #N/A result for entire sheet, when i know that there are matches. Is there a limitation with VLOOKUPS when searching for multiple criteria in a table?
Hi!
Note that the VLOOKUP function can only look up one value. You want to search multiple values at once. To not display an error message, use the IFERROR & VLOOKUP function. Please read the VLOOKUP manual carefully.
=IFERROR(VLOOKUP(C2,$A$2:$B$3111,2,0),"")
Hello,
I am trying to write a formula that looks at three criteria and if all three criteria are met return a name.
I have the names in cells b17 through b36.
The first match I need is in cells e17 through e36. In these cells the number can be <= 2
The second criteria is in cells f17 through f36. In these cells the numbers can be 9.
If all three of these criteria falls within the range given, I need the result to be the name listed in cells b17 through b36
I have followed the match index but it’s not working for me.
If you can let me know if this can be done I am great full.
Thanking you in advance, Chuck Vaughan
Hi!
Please clarify your question. Should the condition be true for all cells in the range or just one of them? Is the result of the formula also a range of cells?
Great source of how to use Lookup functions.
Is there any way to make the lookup_array dynamic or a computed value (without using named ranges that are defined)? I've tried using the indirect function as you have but in the form of
=VLOOKUP(lookup_value,INDIRECT(B2)&":"&INDIRECT(D2), columnIndex, rangeLookup)
where B2 and D2 are the corner points of the desired array (in the form of $f$10 and $p$100)
array 1 $f$10:$p$100
array 2 $q$10:$aa$100
array 3 $ab$10:$al$100
etc...
Using defined named ranges creates additional workload and using a fixed lookup_array creates a massive array.
sorry, I was using the Indirect function incorrectly, but using the equation
=VLOOKUP(lookup_value, B2&":"&D2, columnIndex, rangeLookup)
just gives me a '#value' error because apparently B2&":"&D2 is evaluated as the string "$f$10:$p$100" and not the range $f$10:$p$100.
my apologies again, after some additional trial and error the following works - but thanks for your tutorial it definitely helped in solving my problem.
=VLOOKUP(lookup_value, INDIRECT(B2&":"&D2), columnIndex, rangeLookup)
Name/date 7/22 7/23 7/24
name1 65 55 22
name2 0 22 19
name3 2 59 0
Hi pls refer to the table I want to know when I select date I want to get cell values 1st highest to low then i need to get corresponded row value in front of that number
Say I Select 7/24
the result should be:
22 name1
19 name2
Hi!
The following tutorial should help: Excel INDEX MATCH MATCH and other formulas for two-way lookup.
Please try the following formulas:
=LARGE(INDEX(B2:E4,, MATCH(H2,B1:E1,0)),1)
=INDEX(A2:A4,MATCH(LARGE(INDEX(B2:E4,, MATCH(H2,B1:E1,0)),1), INDEX(B2:E4,, MATCH(H2,B1:E1,0)),0))
where H2 = "7/24"
The LARGE function with an argument of 1 specifies the highest value in the range. For the second value, change 1 to 2.
I hope it’ll be helpful.
2 sheets with addresses, trips sheet and jobs sheet. Trips is from fleet software tracking address of vehicle. Jobs sheet is job address and job data including job#. I need a column on trips sheet that looks to the job sheet addresses, finds the clise match and returns the job #.
Have tried vlookup and indexmatch.
Hello!
Have you tried the ways described in this blog post? It contains answers to your question. Also pay attention: Vlookup from another sheet.
I can't offer you a formula as I don't have your data.
Does the author issue any Email Seminars or thoughts? She is truly one of a kind - great Excel Seminars and would truly appreciate being advised of any & all seminars she might offer.
Thoughts?
Being researching Excel seminars for the last few decades & have found she is the leader - best
Thank you for your kind words, Waldo. I do not run any email seminars. You can find all my Excel articles on this blog.
I have inventory spreadsheet from month to month. The ending inventory of the previous month is the beginning inventory for the current month. Sample Formula for the current month =IF(ISNA(VLOOKUP(V2,Mar22!C:D,2,FALSE)<=0),0,(VLOOKUP(V2,Mar22!C:D,2,FALSE))). The formula works, however, I want the negative balance to show as "0" for the following month. Please help. Thank you
Hello!
Add one more condition to the formula with a nested IF function. I can't check the formula that contains unique references to your workbook worksheets.
=IF(ISNA(VLOOKUP(V2,'Mar22'!C:D,2,FALSE)),0, IF(VLOOKUP(V2,'Mar22'!C:D,2,FALSE)>0, VLOOKUP(V2,'Mar22'!C:D,2,FALSE),0))
Hello,
I am attempting to retrieve certain data using a unique identifier (123456), points from another sheet onto the main one I need the data on though there are multiple data points.
This the formula I am using but keep getting an error:
=VLOOKUP(A2,INDIRECT("A"&(MATCH(A2,Gradebook!$A$2:$F$2891,0)*ROW(Gradebook!A1:A2891))&":M2891"),6,FALSE)
One tab in the workbook is titled Main and these are the data points (below):
Student ID First Name Last Name Grade P1 Course P1 Mark P2 Course P2 Mark P3 Course
123456 Student Test 9
Which I am trying to pull the data points from tab titled, Gradebook, that contains the data points below
Student ID Student Name Course Periods Mark Perc
123456 Test, Student Literature 12 P1 C 72.33
123456 Test, Student Chemistry P2 F 57.28
123456 Test, Student Geometry P3 D 60.53
123456 Test, Student Theater P4 B- 80.25
123456 Test, Student Ethnic Studies P5 B- 80.35
123456 Test, Student Fitness P6 C+ 78.92
Which formula I can use, how can I pull the data points from Gradebook to paste onto the Main tab under each column?
Thank you!
Hello!
With an Excel formula, when you search by multiple criteria, you can extract only one value. For example, you can find the Mark value by the criteria Student ID, Course, Periods.
I recommend reading this guide: Excel INDEX MATCH to look up multiple criteria.
I hope my advice will help you solve your task.
=INDEX(Gradebook!$G$2:$G$2891,SMALL(IF($A2=Gradebook!$A$2:$M$2891,ROW(Gradebook!$A$2:$M$2891)-1,""),1))
I found this formula and it pulls the data I need but it is possible for it to pull data from a column based on data from another column?
For example:
Student ID 123456 has 3 columns of data
Column A: PE
Column B: Period 1
Column C: A+
How can I pull from any data point from Column A when column B contains specific text such Period 1, Period 2, etc?
Hi!
Sorry, it's not quite clear what you are trying to achieve.
I been working to recreate this seminar and have a few questions:
1)How to Vlookup and return multiple values in Excel - utilize INDEX, SMALL & ROW functions section Formula - {=IFERROR(INDEX($C$2:$C$11, SMALL(IF($F$1=$B$2:$B$11, ROW($C$2:$C$11)-1,""), ROW()-1)),"")}
If the cell containing this formula is C250 how does the above change? Should the "ROW()-1" become "ROW()-250? Can't get this to work
2)Name Range "Product" in one of your sections you state the range for Product as B2 It should shown as B2:B11
Thoughts?
Your seminars are one of the best if not THE BEST - many thanks Outstanding & very educational
Your Section - How to do multiple Vlookup in Excel (nested Vlookup) - 2 subanalysis to VLOOKUP 3rd file
Shows the "Products" range as D3:E3 believe it should be D3:E10
Shows the "Prices" range as G3:H3 believe this should be G3:H10
Thoughts?
Thanks
You are absolutely right, fixed. Thank you for pointing out that mistake!
Hi Waldo,
1) The generic formula is this:
IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range ) - m ,""), ROW() - n )),"")
Where:
- m is the row number of the first cell in the return range minus 1.
- n is the row number of the first formula cell minus 1.
Assuming both the first cell in the return range and the first cell containing the formula are in row 250, you formula may look something like this:
=IFERROR(INDEX($B$250:$B$260, SMALL(IF(D$249=$A$250:$A$260, ROW($B$250:$B$260)-249,""), ROW()-249)),"")
For the detailed explanation, please see How to Vlookup multiple matches and return results in a column.
2) Can you please specify the section's name? Cannot find it.
I am trying to use this =VLOOKUP($A4,Data2!$A:$AC,H$1,FALSE) to pull forecast for multiple months from data file but this does seems to be working, could you please walk me through to use this formula appropriately ?
Hi!
Without seeing your data, I can't verify this formula.
I'm looking for a solution to work around using vlookup + vlookup
The data set is something like this:
10
20
31;32
40
The current idea is to insert enough columns to separate all items, then iferror(vlookup,,,),0) + iferror(vlookup,,,),0) + iferror(vlookup,,,),0) to sum all instances, or manually overwrite the single vlookup on the lines where multiple items are needed
Hello!
Sorry, I do not fully understand the task. Could you please describe it in more detail? What result do you want to get?
Hi. Can you please tell me what exactly do the following formulas yield. PLEASE!
=VLOOKUP(C2,M2:N180,2,0)
=VLOOKUP(C9,M:M,TRUE,FALSE)
Hello!
In this article, you can read the detailed description of the VLOOKUP function.
Hi There
i worked in logitics company where i need to to find Vlookup 1,2 and 3 occurence value that are in same column against in a order and want answer in column 1, 2 and 3 and then if blank choose other option kindly help me , to fix this
Hi!
I don't quite understand what you want to find. Explain with an example of your data.
I have an excel data like following, i want to Securitate only work completed line items in another work sheet, which formula we can use in VLOOKUP
SL.NO. QTN STATUS QTN REFE NO.
2 PENDING CS-QTN-06-21-0002
3 WORK COMPLETED CS-QTN-06-21-0003
4 REJECTED CS-QTN-06-21-0004
5 WORK COMPLETED CS-QTN-06-21-0006
6 WORK COMPLETED CS-QTN-06-21-0007
10 PENDING CS-QTN-06-21-0013
11 WORK COMPLETED CS-QTN-07-21-0005
17 PENDING CS-QTN-07-21-0017
18 PENDING CS-QTN-07-21-0018
Hi!
Sorry, it's not quite clear what you are trying to achieve.