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)
253 comments
Hi! I feel like I should be able to get this but my brain is not braining!
I have 2 documents. One has the source data, one has the results data.
I want to bring a particular value from the source data book to the results data book, based on 3 columns of criteria.
Source Data
Column A = Workplace
Column B = Recruiter
Column C = Approver
Column D = Count of Approved
I want to bring the value of Column D from Source Data into my Results Data book, as long as Columns A through C match. If columns A through C do not match I want to return a 0 value.
The Results Data book is set up mostly along the same lines, with some additional columns with other results not relevant to this initial data search.
Is this possible?
Thanks in advance :)
Hello Rachael!
This information is insufficient to provide you with a precise formula. To get value from the table that matches 3 criteria, you can use these instructions: Excel INDEX MATCH with multiple criteria - formula examples.
To correctly create references to another document in a formula, use this guide: Excel reference to another sheet or workbook (external reference).
Hello,
I need a help please.
My Data looks like as below.
This Data tells about Employee Qualities. If it is Yes, means that respective Employee has the Quality.
I need to create a Formula which will return this "Yes" qualities of the Employees for Selected Employee Name from a Drop Down in Same Worksheet
Data below is spread across A1:F8 and Employee Selection Drop Down at A15
For Example, If I select Employee 1 from A15 cell drop Down, A16 and below rows should return data like
Punctual. On Time
Eager to Learn new things
Flexible
Takes initiatives
Remarks Employee 1 Employee 2 Employee 3 Employee 4 Employee 5
Punctual. On Time Yes Yes Yes Yes Yes
Good Communication Skill No Yes Yes No No
Eager to Learn new things Yes Yes No Yes Yes
Quick Learner No Yes Yes Yes Yes
Flexible Yes Yes Yes Yes Yes
Takes initiatives Yes Yes No Yes Yes
Crossed Skilled Resource No No Yes Yes Yes
Thank you
Hello Chetan!
If I understand your task correctly, following formula should work for you:
=FILTER(A2:A8,INDEX(A2:F8,,MATCH(A15,A1:F1,0))="yes","")
MATCH function determines number of data column corresponding to value in A15.
INDEX function extracts this column.
FILTER function extracts from first column the values corresponding to "yes".
You can also find useful information in this article: How to Vlookup multiple values in Excel with criteria.
You are truly amazing buddy !!.
Your formula worked Exactly the way I wanted
Great Man, Great Help !!
Thank you So much you have helped me to save 3-4 hrs a day for manual work.
Hi Alex,
Hope you are doing well, can you please help me to find out solution for my problem. I have two sheets:
Sheet-1(Correct One)
Customer Product Code Quantity Amount
Alko 1 10 100
Montyl 4 2 140
Montyl 3 20 1000
YoYo 3 1 50
Frank 2 11 220
Marias 1 20 200
Marias 2 5 100
Marias 2 4 80
Erina 4 3 210
Aulis 3 7 350
Aulis 1 6 60
Siram 2 1 20
Flora 4 10 700
Sheet-2(InCorrect One)
Customer Product Code Quantity Amount
Alko 1 10 100
Alko 1 1 10
Montyl 3 20 1000
YoYo 3 3 150
Frank 2 11 220
Marias 1 20 200
Marias 2 4 80
Erina 4 3 210
Aulis 3 5 250
Siram 2 1 20
Now I wanted to find via Vlookup or any other functions which rows are correct in Sheet-2 (after comparing with Sheet-1) and which are incorrect (to clearly tell me which field(s) or row I need to update or insert or delete). I have almost 400K data in actual Sheet-1 & 350K data in actual Sheet-2, so I am trying to find some formula for these few values/fields so that I can apply . Thanks in advance for your help.
Hi! You can use the MATCH formula to find matching rows in tables. If match is found, the ISNUMBER function returns TRUE. Use the IF function to write the result of the comparison in the cell. For example, try to enter the following formula in cell F2 on Sheet2 and then copy it down along the column:
=IF(ISNUMBER(MATCH(A2&B2&C2&D2,Sheet1!A:A&Sheet1!B:B&Sheet1!C:C&Sheet1!D:D,0)),"Match","No")
We have a tool that can solve your task in a couple of clicks without formulas. Compare Sheets - tool to compare worksheets in Excel.
It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Good Day: How would one use multi factor lookup with numeric data specifically where the second criteria would use the range_lookup=True to find something in between values in the second column. Example below.
Using the table below, criteria provided would be year (e.g. 2025) and Amount (e.g. 120). Desired return 2025, 120 would be 21.0% for col 3 return and $2100 for col 4 return
Year Amount Pct Payment
2024 0 10% $1,000
2024 100 20% $2,000
2024 200 30% $3,000
2024 300 40% $4,000
2025 0 10.5% $1,050
2025 115 21.0% $2,100
2025 230 31.5% $3,150
2025 345 42.0% $4,200
2026 0 11.0% $1,103
2026 132. 22.1% $2,205
2026 264 33.1% $3,308
2026 396 44.1% $4,410
2027 0 11.6% $1,158
2027 152 23.2% $2,315
2027 304 34.7% $3,473
2027 456 46.3% $4,631
Thanks in advance: Dan
Hi! You can use INDEX MATCH to search for a value based on two conditions. For the detailed instructions, please see: Excel INDEX MATCH with multiple criteria - formula examples.
=INDEX(C2:C20,MATCH($F$1,B2:B20/(A2:A20=$E$1),1))
F1 = 120. E1 = 2025.
For MATCH function, use the approximate search as described in this manual: How to use MATCH function in Excel. This should solve your task.
Thanks for your reply. Your suggested formula returns #VALUE!. My exact formula is below (I modified my sheet to match your inputs and the range of the data)
=INDEX(C2:C17,MATCH($F$1,B2:B17/(A2:A17=$E$1),1))
Double clicking the formula highlights the correct ranges and criteria cells.
When doing Evaluate Formula, ranges B2:B17 and A2:A17 both evaluate to #VALUE! (in that order). Both F1 and E1 come in properly.
What am I missing?
Hi! The formula I sent to you was created based on the description you provided in your first request. The formula works correctly with this data. Check your data.
Nevermind: I just caught that this should be an Array type of formula (CTRL-SHIFT-ENTER).
Thanks
Which Excel formula should I use to have the number of actions from the first column (OWNER) that are closed (COLUMN 8 (STATUS Open / In Progress / Closed)
In the First column you will have different B numbers (B01, B02, B03, B04, etc.)
Hello Carlos! If I understand your task correctly, this guide may be helpful: COUNTIF with wildcard characters (partial match). Please check the formula below, it should work for you:
=COUNTIF(A2:A10,"*Closed*")
Hi!
I’m having trouble sorting with a if(xlookup) formula in a spreadsheet.
I have a if(xlookup) set in columns C-H where it will automatically populate information once there’s values in Columns A and B. The formula is carried into blank cells but hidden with (if(xlookup)=o,””,(xlookup) formula. Now when I try to sort, the blank cells (with the hidden formulas in them) appear right at the top of the sort. Is there any way to stop the blank cells to appear on top? Thank you for your help!
Hello Anton! Excel does not recognize such cells as empty when sorting if a formula is written in a cell that returns the empty string "". To ignore such cells when sorting, you can use an Excel filter to sort the cells. Filter out these cells, then sort. For more information, please visit: Filter and alphabetize in Excel.
Hi! I’m having trouble nesting a IF-Vlookup formula to hide 0s
The formula I currently have is
=IF(VLOOKUP(B2, Jan!C:G,5,0)=0,””, VLOOKUP(B2, Jan!C:G,5,0)
I’m able to pull from Jan sheet but would also like to nest in Feb!, Mar!, Apr! Sheets into this formula. How would I go about it ?
Thank you for your help!
Hello Dmitry!
If I understand your task correctly, following tutorial should help: How to VLOOKUP across multiple sheets in Excel with examples. Based on your information, formula might look something like this:
=IF(IFERROR(VLOOKUP(B2, Jan!C:G,5,0), IFERROR(VLOOKUP(B2, Feb!C:G,5,0), "Not found"))=0,"", IFERROR(VLOOKUP(B2, Jan!C:G,5,0), IFERROR(VLOOKUP(B2, Feb!C:G,5,0), "Not found")))
To avoid displaying zeros in cells, you can also use a custom number format. This is described here: Display zeros as dashes or blanks. In this case, you don't need to use IF function.
I have 2 sheets - say Sheet A and B. How do I vlookup multiple values from Sheet B based on vlooked up with a specific field in Sheet A. Something similar to this formula but need to vlookup from 2 different sheet - {=IFERROR(INDEX($C$2:$C$11, SMALL(IF($F$1=$B$2:$B$11, ROW($C$2:$C$11)-1,""), ROW()-1)),"")} Can you kindly help.
Hi! The INDEX function in your formula can only extract one value from a range of data. You can use INDEX MATCH to do this.
=IFERROR(INDEX($C$2:$C$11, MATCH($F$1,$B$2:$B$11, 0)),"")
To extract multiple values by condition, try instructions above.
Or you can try the FILTER function as described in this article: Excel FILTER function - dynamic filtering with formulas.
Unfortunately, your information is not enough to give more detailed advice.
I am using the VLOOKUP INDIRECT advice however when I plug in this formula in my desktop version
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
my excel throws a #REF error
how do I correct this?
Thank you,
Hi! I don't know what kind of reference you were trying to get with the INDIRECT function, and I don't know what is written in cell B1.
If you wanted to get a reference to a named range, check what name is returned by B$1&"_Sales". For the detailed instructions, please see: How to use INDIRECT function in Excel - formula examples.
Your example of INDIRECT VLOOKUP seemed to have missed some parameter because as is, it gives me a "Volatile" result... once I add the reference column/rows then it works and brings me Oranges from CA_Sales. I just don't know why in your online excel version, your formula works but not when you do it in desktop version. Thank you
=VLOOKUP($A2, INDIRECT(B$1&"_Sales!A1:B5"), 2, FALSE)
Hi! You can download the sample file at the end of this article and see that all of these formulas work.
How can you use this if there is a an "OR" for your criteria? Like if it can't find a name, it should look up a different column by phone number, and if there is a match with either it should return a specific column?
Hi! If I understand your question correctly, this article may be helpful: IF VLOOKUP in Excel: Vlookup formula with If condition.
How can I get VLOOKUP to do this?
Column A has many values, some of them duplicates. Not all of the duplicates have a value in column B.
When i enter a new value in cell A1000 (Mike) i want to have a formula in cell B1000 like this:
=VLOOKUP(A1000, $A$1:$B999,2,0) but if the value is blank then keep searching until you find the next "Mike" column A that does have a value in its corresponding B cell.
in the case below I'd like to return a value of "ALIEN"
Row # Column A Column B
1 Mike (blank)
2 John cat
3 Anna dog
4 Beatrice man
5 Mike
6 Terry bird
7 Francis worm
8 Mike ALIEN
1000 (New entry (formula here to find the first non-blank cell in column B for Mike)
named) Mike
figured it out:
=IFERROR(INDEX($B$2:$B999,MATCH(1,IF($A$2:$A999=A1000,IF($B$2:$B999"",1)),0)),"")
Hi! You can find the desired non-empty value using the INDEX MATCH function. Based on your information, the formula might look something like this:
=INDEX($B$1:B999,MATCH(1,($A$1:A999=A1000)*($B$1:B999<>""),0))
You can find the examples and detailed instructions here: Excel INDEX MATCH with multiple criteria - formula examples
Hi. I am trying to do a VLOOKUP that searches for multiple lookup values. For example, I know this syntax doesn't exist, but I want to achieve something like this =VLOOKUP(H4 OR H5, A1:B10,2,). My return values in this case are all numbers, so ideally I'd like it to look for any values matching cells H4 or H5, then sum all corresponding values and return. In this case there may be a value only in H4, only in H5, or both. Is this possible??
Hi! To sum values by condition, use the SUMIF function. For example:
=SUMIF(A1:A20,H4,B1:B20)+SUMIF(A1:A20,H5,B1:B20)
Also, to find the sum by multiple criteria, try the SUMPRODUCT function:
=SUMPRODUCT(((A1:A20=H4)+(A1:A20=H5))*B1:B20)
I have a worksheet with different file numbers for the same account
Account number is in column A and file numbers in B
Please give me a formula where it will give me all the multiple values in column B for an account number in column A
Thank you
Hi! Look for the example formulas here: How to Vlookup multiple values in Excel with criteria.
Hello, I am looking for formula where I have a row on product with multiple information in columns. I have to base information from two column and return back value from column 1. For example:
Houses Cars Office
Matt 3 2 2
Venkat 4 1 3
Lars 2 3 6
Sam 8 4 5
Output I want to know person with 2 houses and 1 car
I want to know person with 3 houses and 4 cars
How can this be done?
Hi! If I understand your task correctly, you can find the examples and detailed instructions here:: Excel INDEX MATCH with multiple criteria. The formula might look like this:
=INDEX(A1:A10,MATCH(1,(B1:B10=2)*(C1:C10=1),0))
I'm looking for a formula that will look up a date in column A, then lookup a name in column C, then look up a category from row 1, and return the value when all three has been met.
I have tried using the following =INDEX(AU2:DJ139,MATCH(C2,AU2:AU5000)*(B21,AW2:AW5000)*(F20,AU1:DJ1),0) but get #N/A returned.
Any suggestions as to what formula will look up all three and return the value would appreciated.
Hi! To do a multi-criteria search, look for the example formulas here: Excel INDEX MATCH with multiple criteria.
Also note that all ranges in the formula must be the same size.
I hope I answered your question. If something is still unclear, please feel free to ask.
I am using the VLOOKUP function to search for values in one workbook (Workbook 2 cells: F513 = 515,600.05 and cell F518 = 96,560.46) 6 columns to the right of column A, based on multiple values in another workbook, within ONE cell (Workbook 1 cell A15 = 401-05-0000, 403-01-0000. I would like the results to be added together and the result placed in one cell (Workbook 1 cell G15. How should I modify the VLOOKUP function below at the red arrow to do this?
=VLOOKUP(A15,'[BVAR Balanza julio 2023.xlsx]Balanza de Comprobación'!$A$387:$F$834,6,FALSE)
Thank you
David
Hi! To sum multiple values based on several criteria, try using the SUMIFS function. Read more: Excel SUMIFS and SUMIF with multiple criteria – formula examples.
Sorry, ignore "at the red arrow to do this?". I could not post a screen shot. I just want to know how to modify the VLOOKUP to accomplish the above.
Thank you,
David
I want formula for two sheet vlookup then i want c column value against b& a
? I have sheet for real estate customer payment for rent each customer with deferent code number have how can i make the formula for each code that remind me to pay then the status for each customer after unpaid show paid on that date.
please support me
Hi! Look for the example formulas here: How to VLOOKUP across multiple sheets in Excel with examples.
Hello! If I understand you correctly, for the "Actual" column, try this formula:
=IFERROR(INDEX(Sheet1!$D$2:$D$12,MATCH(1,(Sheet1!$A$2:$A$12=Sheet2!A2)*(Sheet1!$B$2:$B$12=Sheet2!B2),0)),"no match")
For more information, please visit: Excel INDEX MATCH with multiple criteria - formula examples. I hope my advice will help you solve your task.
Hey Alexander. I tried again with the formulas present on the page Excel INDEX MATCH with multiple criteria - formula examples", which you shared with me in your above comment.
It worked absolutely fine and my sheet is fetching data as expected!
Thankyou so much for your help and time !!!!
hello i want to find age (how old as on date) in excel with year and months only, if days in a month is 15 or more , it round off to next month.
Hi! Why are you asking this question in an article about VLOOKUP? Try to use the recommendations described in this article: How to calculate age in Excel: from date of birth, between two dates.
Doesn't look like there is a solution to the following: I have a master list of agents. This list shows if they pick up or courier documents. However, the report I must check names against are in 2 different columns. Meaning there is a sell agent column and a buy agent column. I cannot combine the columns. So, I want to check Column 1 and return if the name is found - PU or Courier. If the name is not found, I want to check Column 2 and return PU or Courier. I have searched all over the web, and there is no solution to check 2 different columns and return information. Seems EXCEL should allow you to find this data via a formula solution. If anyone out there has a solution it would be a huge help to publish the steps. I am new to excel, and cannot do visual basic/code. I run this report daily, and right now I am printing the columns and manual checking the list, however there are more than 400 names. Help?
Hi! If I understand your task correctly, the answer to your question can be found in this article: VLOOKUP with IF statement in Excel.
IF(ISNA( VLOOKUP(…column1....)),IF(ISNA( VLOOKUP(…column2....)),"No","PU"),"PU")
I hope my advice will help you solve your task.
Hey Alexander,
Thankyou so much for looking into my problem statement.
I tried your solution and referred to the page that you have mentioned.
Unfortunately, none of the solution is working for me.
One thing I want to try from my end and for that could you please help me know the Vlookup formula if the columns are not in series. E.g, in sheet1 I want to check values of column A and B and fill values in column D and I want to match and fetch from sheet2 (Match: sheet1 column1= sheet2 column1, Match: sheet1 column2 = sheet2 column2 and Fetch from: sheet2 column 4 -> sheet1 column4)
Hi! If I understand your task correctly, the following tutorials should help: INDEX & MATCH in Excel - better alternative to VLOOKUP and INDEX MATCH with multiple criteria - formula examples.
I already answered you earlier.