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 4 columns in sheet1 (Job No, Operation Code, Budget, Actual) and I have same 4 columns in sheet2 too(Job No, Operation Code, Budget, Actual).
I want to fill columns Budget and Actual of sheet2 automatically when the Job No and Operation Code matches in both the sheets. I tries using Index, Match and Vlookup but all are giving me either incorrect values or no match even though exact match is present. Giving here sample data. Please, can anyone help me in this case? I am really trying to find solution and learn new tricks in excel. Thankyou in advance.
Sample data of Sheet1:
Job No Operation Code Budget Actual
Job01 Design 0 5.27
Job02 CNC 0 30.97
Job03 Design 0 9.92
Job04 CNC 0 23.35
Job05 EDM 0 4.78
Job06 Grinding 0 30.32
Job07 ManualLabor 0 1.03
Job08 Design 0 281.27
Job09 ManualMachining 0 24.52
Job10 CNC 0 1287.17
Job11 EDM 0 102.88
Data in Sheet2:
Job No Operation Code Budget Actual
Job01 Design ? ?
Job02 CNC ? ?
Job03 Design ? ?
Job04 CNC ? ?
Job05 EDM ? ?
Job06 Grinding ? ?
Job07 ManualLabor ? ?
Job08 Design ? ?
Job09 ManualMachining ? ?
Job10 CNC ? ?
Job11 EDM ? ?
I need to fill in cells where ? is present with correct matching values from sheet1.
What I have tried:
1) For "Budget" column match:
=IFERROR(INDEX(C$2:C$12,MATCH($A16&$B16,$A$2:$A$12&$B$2:$B$12,0)),"no match")
For "Actual" column match:
=IFERROR(INDEX(D$2:D$12,MATCH($A16&$B16,$A$2:$A$12&$B$2:$B$12,0)),"no match")
These gives me "no match" even thought there is a match
2) For Budget column match:
=VLOOKUP($A16&$B16,$A$2:$C$12,3,FALSE) ----> Gives #N/A, even though there is a match.
For "Actual" column match:
=VLOOKUP($A16&$B16,$A$2:$B$12&$D$2:$D$12,3,FALSE) ------------------> Gives #Value, even though there is a match.
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.
Hi there,
I tired this formula:
=INDEX('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$AI$9:$CM$28,MATCH(B7,'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$C$9:$C$28,0),MATCH('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$L$5&'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$AI$8,'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$L$5:$CM$5&'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$C$8:$CM$8,0))
for the below and just can't get it right.
I have two Excel sheets Construction Payroll Hrs 2023 & Payroll File 2023. In Payroll File 2023, I need the Breakdown section (OT 1.5, OT 2, Holidays, Bank Holidays, Sick Paid, Sick Unpaid) to be filled out based on the week number in B5. This information will be taking from the Construction Payroll Hrs 2023. So number of let's say overtime 1.5 hours will match the Employee ID as well as the specific week i.e. in week 18, employee ID 1 will have the following hrs: 4.5 in OT 1.5, 10 in OT 2, 8 in bank holiday etc. These numbers will change depending on the week. I hope it makes sense.
Would you be able to help me out? (I couldn't attach the sheets unfortunately).
Many thanks.
Lucie.
I used the below formula and it works for the very first cell but doesn't change with the date. Might be a starting point at least?
=(INDEX('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!AI9:ME9,SMALL(IF('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!L5:MI5=B5,ROW('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!AI9:ME9)-MIN(ROW('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!AI9:ME9))+1),COLUMNS(B5:B5))))
It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.
Hi, thank you for the reply. Apologies, let me explain.
I have two spreadsheets - one is called Construction - Payroll Hrs 2023 and the other one is called Payroll File 2023. The Payroll File 2023 needs to extract data from Construction - Payroll Hrs 2023.
I will adjust the column letters and numbers so it makes a bit more sense.
So let's say that in Construction - Payroll Hrs 2023 I have the following info:
- Employee ID: A5:A28, with the column heading in A4. Employee ID will always stay in column A.
- From column B, I will be inputting employee weekly hours, including overtimes, holidays, etc. This sheet is set up for the whole year so there
will be different amount of hours in every week. Week 1: Basic hours will be in B5:B28, with the column heading in B4. This will change in
week 2 where the basic hours will be in let's say column I (7 days / week therefore starts from 8th column) - I5:I28 with the heading in I4.
- Another important info in this sheet is the week number: the week number is placed two lines above the basic hours heading (this will also
change with the week) B2 for week 1 and I2 for week 2.
Because I can't have the two excel sheets together, I had to create the Payroll File 2023.
In this sheet, I have a dropdown list to specify the week I'm looking for: So let's say in B3 I have the dropdown to search for week 1, week 2, etc. Two lines under that, in B6 I will have Employee ID heading and it will list ID numbers from B7 down. In C6 I will have basic hours heading .
If in this sheet I select week 1, I need it to extract my basic hours etc. from the Construction - Payroll Hrs 2023 - so for week 1 it will take info from B5 to B28 and if I select week 2, it will take the info from I5:I28 etc.
So I need my basic hours in second sheet to change based on the dropdown (week) selection and Employee ID number.
I hope this makes a bit more sense?
Really appreciate your time. Thank you.
Hi! To dynamically change the range B2:B28 depending on the week number, use the OFFSET function. For example, B3 is a drop-down list with the week number.
=OFFSET('1'!B2:B28,0,('2'!B3-1)*7)
For more information, please visit: Excel OFFSET function - formula examples and uses.
I am trying to formulate cell B16 on sheet 1 to look up a value from a table with number ranges in sheet 6 based on data in cell B14 & B15 in sheet 1.
Please see below example for clarity on the query:-
B14: 12 (nominal diameter of a bolt)
B15: 1.75 (pitch)
B16:-needs to lookup up the nominal diameter firstly in sheet 6 between column A and B
- the value falls under a number range between two cells. E.g A18 is 11.2 and B18 is 22.4 - 12 falls in between
- Another lookup function will be needed for the value in B15 sheet 1 - to be matched with column C in sheet 6
- Once 12 and 1.75 have been matched - the value needed to be displayed in B16 will be displayed in column 4 in the table.
Which excel function would be most suitable to use in this scenario?
Hi!
To find the value between two numbers, you can use the formula INDEX MATCH. For more information, read: Excel INDEX MATCH with multiple criteria - formula examples.
=INDEX('6'!D2:D200,MATCH(1,('6'!A2:A200<=B14)*('6'!B20:B20>=B14),0))
Unfortunately, I could not understand the rest of your questions. If you explain, I will try to help.
I have a workbook with 10 different tabs each tab has a different supplier price list. I would like to create a quote tool on the first sheet were if you add the part number it will search all 10 tabs for the description and price. would you be able to assist with the formula
Hi! Here is the article that may be helpful to you: VLOOKUP across multiple sheets in Excel with examples.
I am trying to use value from C2 to look up the array from M2 to Q6 and return column O value. However, the C2 value falls between 2 values in the array. Microsoft Excel's default is to use the smaller value (e.g. if C2 = 38, The look up values in the array are 9, 15, 25, 37, and 50. Since C2 value is between 37 and 50 in the array, Excel returns the value for 37. I want it to return the higher value instead (that is 50 in this example). HOw can I write this VLookup?
The table looks like this and the value from C2 changes. we can have 38, 29, 19, 11
#(column M) Name Code
9 QB E12
15 TE E13
25 RB E14
37 WR E15
50 DE E16
Hi! Use an approximate search with INDEX MATCH and increase the number of the position that was found by 1.
Try the following formula:
=INDEX(O2:O6,MATCH(C2,M2:M6,1)+1)
For more information, please visit: INDEX & MATCH in Excel - better alternative to VLOOKUP.
I have two individual spreadsheets with an item number & and address on both. I will need to place a file # on the spreadsheet that does not have it. How ever when I do the Vlookup I receive an error. However the Item number repeats at every change of address.
for each single address I have 15 items number and I need the File # for each address and item number.
Please help :(
I am not sure I fully understand what you mean. To understand what you want to do, give an example of the source data and the desired result.
I have an excel sheet that i record the products that i sell. It has the following columns, i.e Quantity, price per unit,total sale, amount paid and outstanding amount. Quantity and price per unit i input manually. Total sale =Qty x price per unit. For payments received i use another excel sheet. How best can i merger payments from payments excel sheet to the column for amounts paid considering that payments are paid in instalments. How best can this be reconcilled without inputing them manually on the amount paid column, retrieving from payments column
Hello! To summarize the payments for each product, I recommend using the SUMIF function. See this manual for detailed instructions and examples: How to use SUMIF function in Excel with formula examples.
I have doubt in vlookup function how to select a "table array" as another cell data
like =VLOOKUP(D2,AA2,18,0)
D2 is the lookup value
AA2 is the lookup range
18,0) is [range_lookup]) cell of the lookup table
i have "table array" data in one cell.
Hi!
If you entered a table array in cell AA2, its values are written in a range of cells. For example, AA2:AB20. Use this range in the VLOOKUP formula.
Maybe this article will be helpful: Excel dynamic arrays, functions and formulas
Hi I need help.
Sheet 1 = employee data
Sheet 2 = leave request data from employees
Sheet 3 = leave tracker with employees id in rows and dates in rows.
I want to show in sheet 3 two things, either "plotted leave" or blank.
'Plotted" will show if the data is equal to the employee id and the leave requested, else it will show as blank.
Thanks for your help.
Hi!
I can't see your data, so I can't recommend a formula. To find the employee ID on Sheet2, use the MATCH function.
The formula could be something like this:
IF(ISNUMBER(MATCH(ID,.......)),"Plotted","")
I have 2 Excel files.
Excel 1 contains all the projects (raw data)
Excel 2 contains all the tasks created for projects in Excel 1
There are projects with multiple tasks created on them and I want to show all those tasks in one column
Is that possible?
Hello!
If you want to show in one column all tasks related to one project, use this guide: Vlookup multiple matches in Excel with one or more criteria.
If you want to show other data in several columns besides tasks, use the FILTER function. Read more: Excel FILTER function - dynamic filtering with formulas.
I hope my advice will help you solve your task.
I've read through these, but a bit lost on the best approach. I'm hoping two sheet vlookup might work.
sheet 1 has name and appointment date.
sheet 2 has name, appointment date, appointment status.
Sheet 2 will have multiple rows for the same name, and different appointment dates for that name.
i need to pull the appointment status in sheet 2 to match with the name and appointment date in sheets 1 and 2.
Any suggestions? Thanks in advance!
FJD
Hello!
Here is the article that may be helpful to you: VLOOKUP across multiple sheets in Excel with examples.
Thanks so much!
Hello I need some help with some excel formula with the data below:
Column A will have a list of names
column B will be that person's rest day
column F will have a list of dates (1 date per cell) for vacation
column H will have the name who made the request.
This is what I want to achieve, in Column C, this will tell if a person is on shift, rest day, or on vacation that day. Is this possible?
Thank you for your help.
Hello!
Use a nested IF formula on a column to display a value by multiple conditions.
If I understand your task correctly, try the following formula:
=IF(B1=TODAY(),"Rest", IF(F1=TODAY(),"Vacation","Shift"))
Hi, If you had Lemons sweet and lemons sour but wanted to return both using this example =INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
I have changed G1 to "*Lemons*" but it is not working. How do i return a sum of Col D?
Hello!
If you want to find a partial match of text strings, use the SEARCH and ISNUMBER functions.
For example,
MATCH(TRUE,ISNUMBER(SEARCH(G1,A2:A11)),0)
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Price update Fruit Name Location Price
01-Jan-09 Semangka Jakarta 55555
01-Jan-09 Semangka Bogor 55556
01-Jan-09 Semangka Bandung 55557
01-Jan-10 Apel Jakarta 50000
01-Jan-10 Apel Bogor 60000
01-Jan-10 Apel Bandung 70000
01-Jan-11 Jeruk Jakarta 34343
01-Jan-11 Jeruk Bogor 44343
01-Jan-11 Jeruk Bandung 54343
01-Jun-16 Semangka Jakarta 88888
01-Jun-16 Semangka Bogor 88889
01-Jun-16 Semangka Bandung 88890
01-Jun-17 Apel Jakarta 65000
01-Jun-17 Apel Bogor 75000
01-Jun-17 Apel Bandung 85000
01-Jun-18 Jeruk Jakarta 67676
01-Jun-18 Jeruk Bogor 77676
01-Jun-18 Jeruk Bandung 87676
01-Jan-23 Apel Jakarta 125000
01-Jan-23 Apel Bogor 135000
How to use vlookup formula if i want to know the price of Apel Bogor at Oct 31th 2020?
Hi!
Read carefully the first paragraph of the article above.
how do i select the data filed highest of Wight and highest of Hight
weight Hight
1 0.0 148.3
2 0.0 242.8
3 0.0 123.2
4 0.0 135.5
5 0.0 124.2
6 64.1 72.1
7 84.6 105.6
8 115.4 77.4
9 128.2 77.7
The maximum values of Wight and Hight are in different lines. You can use the MAX function or the LARGE function.
=MAX(A1:A10)
=LARGE(B1:B10,1)
If this is not what you wanted, please describe the problem in more detail.
hi
i have SKU's customer name and sales to those customer for same SKU in multiple days in one month, Need to Vlookup SKU & Customer and get the total sales qty for each SKU for each customer
Hello,
looking for some guidance on a task I am struggling with.
I have to look up the content in coloumn B, based on the content of column A, but cannot seem to get it right.
My struggle is that I do not always get the desired match, as column B contains multiple matches for the text contained in col A, but I would like to return only matches that contain "%B2B%" (this being part of the text contained in column B).
Below an example of my data set:
A B
x ggg
x ggg
x fff-B2B
y ggg
y B2B-aaa
y B2B-aaa
My desired result would be that for the values that I have in column A, my result always displays the match in column B containing "B2B":
A = x ---> fff-B2B
A = y ---> B2B-aaa
I appreciate any suggestions or examples I may adapt to my case.
Thank you very much!
Hello!
To find a partial match between a text string and text, use the SEARCH function.
=INDEX(B1:B10,MATCH(1,--(A1:A10="X")*(--(ISNUMBER(SEARCH("B2B",B1:B10)))),0))
To get the found value by two criteria, use the INDEX+MATCH statement.
Hello Alexander,
thank you very much for your input!
I could solve the issue.
I have a sheet, where I wanted to match data in it if there are any discrepancies I wanted to Identify what are those.
The Sheets typically contains UserIDs in Column and its attributes in rest of the columns like department, first name and last name email address. This file will be manually entered into system by data entry analysts and now I have got the system export file, How can I Identify if there are any discrepancies.
Sheet1(input file for data entry) : UserID First Name Last Name Department Organization CostCenter OrgUnit
Sheet2 (system exported data) : UserID First Name Last Name Department Organization CostCenter OrgUnit
Typically I have rearranged all the columns and now I wanted verify whether the data entered in system is matching with original input file used by data entry analysts.
Hello!
The following tutorial should help: How to compare two Excel files or sheets for differences.
I'd recommend you to have a look at our Compare Sheets - our own 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.