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
Hi, i am using the following VLOOKUP comment which works great! and really could do with this working in Hlookup however it doesnt work, are there different parameters for Hlookup?
Vlookup
Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
‘Updateby Extendoffice
Application.Volatile
Dim xRet As Variant ‘could be an error
Dim xCell As Range
xRet = Application.Match(LookVal, FTable.Columns(1), FType)
If IsError(xRet) Then
VlookupComment = “Not Found”
Else
Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
VlookupComment = xCell.Value
With Application.Caller
If Not .Comment Is Nothing Then
.Comment.Delete
End If
If Not xCell.Comment Is Nothing Then
.AddComment xCell.Comment.Text
End If
End With
End If
End Function
Hlookup
Function HlookupComment(LookVal As Variant, FTable As Range, FRow As Long, FType As Long) As Variant
‘Updateby Extendoffice
Application.Volatile
Dim xRet As Variant ‘could be an error
Dim xCell As Range
xRet = Application.Match(LookVal, FTable.Rows(1), FType)
If IsError(xRet) Then
HlookupComment = “Not Found”
Else
Set xCell = FTable.Rows(FRow).Cells(1)(xRet)
HlookupComment = xCell.Value
With Application.Caller
If Not .Comment Is Nothing Then
.Comment.Delete
End If
If Not xCell.Comment Is Nothing Then
.AddComment xCell.Comment.Text
End If
End With
End If
End Function
Any help would be greatly appreciated ?
i have a some doubt on this
I am having SKu data in one sheet and i have pan india sku data in othr sheet , actully i want find out that first sheet sku qty. Location wise in first sheet against each sku.pls help
The first column is the item number
MAS Code 23% 18% 15% 12.50% 10% 7.50% 5% 5%
10100 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10
10101 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10
10102 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10
10103 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10
i need to pull the commission based on the price that is selected from dropdown list. I dont know how to do that.
One can use the VLOOKUP function with 2 criterias WITHOUT any Helper-Column.
It will become an Array-Formula with some concatenations BUT within the formula.
*** My solution is presented in the below linked picture: https://postimg.org/image/mdmfjj7gd/
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator “Microsoft®” Excel MVP – Excel (2009-2018) ISRAEL
Hi i am very excited about using formulas i excel after reading this page. please help me to solve the problem that i have... i have set of numbers related to my field.. for instance.,
spool no j.no fit up no
16011828-2 6
16014266-1 1OSW
16010115-1 2
16011586-1 2
14010644-1 7
21060012-1 17 24865 / 24842
14060009-1 4
12030178-1 4
14010630-1 4FFW
16011442-1 1
16011925-1 3FFW
16011815-1 2
14010914-2 6
16011440-1 6
12010688-1 2
14010581-3 13
14010088-1 1
14010619-1 1FFW
16090078-1 6
17010191-3 9
12011083-3 7 04574 / 4555
16014313-1 3FFW 21761 / 21766
17010135-2 7
14010658-1 1
16011816-1 4
14010643-2 8FFW
14011316-2 3
12010579-2 7
14010089-1 4
16030591-4 10
16011263-2 13FFW
17010945-2 12
16010822-2 7
12010632-2 7
16090630-3 8FFW
14010659-2 5
16011165-1 4 24199
14010500-2 12
16011829-1 2
12010644-2 13
12010631-1 5
17010205-2 8FFW
16011167-1 3
14010581-1 1FFW
14010660-3 8FFW
16013899-1 2
12010689-2 5
16011842-1 1FFW
16011730-1 6
17010208-3 7
12010551-2 13
17010176-1 5FFW
17010140-1 2
14010676-1 5FFW
16011898-2 12
14010623-2 8
12010688-1 1
17010147-2 11
16013031-2 17
12010641-3 10
12060018-1 2
14010677-2 8
12010553-2 10
12010057-2 14
14011050-2 10
16011852-1 8
17010301-1 1FW
14030082-1 3FFW
16011167-3 11
16011166-2 6
14010498-3 10
like this,, i want to get all the occurrences of my selected spool no,
=IFERROR(INDEX($C$2:$C$63399,SMALL(IF($F$2=B2:B63399,ROW(C2:C63399)-1,""),ROW()-3)),"") I can use the formula,, but i cannot use it continuously,, after finishing one set of calculation..this formula for only one set of calculation,,, and i want to use the array in row vise(spool no only). not column wise,, please any one help me to fix the problem.. thank u..
As you have explained this formula returns the 2nd instance:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
Where do I edit the formula to return the the 3rd 4th 5th etc
Thanks in advance...
I have an tool inventory check out sheet that references a table where there is equipment type column, a description column, followed by an ID number column. I am looking for a formula that after the equipment type is determined, the descriptions are limited to that equipment type,and then the equipment ID number.
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Dear Sir/madam
I tried if condition with vlookup.I got the required result for few cases.Though the required data is there in the next sheet i couldn't get that.How can i get that for remaining cases.
Thank you for this article! It was immensely and has worked quite well.
The only issue I am having is that it is retaining the spaces between cells with the output...for example, in your sample above for all occurrences of lookup value (duplicates), Dan Brown’s Biscuits and Dan Brown’s Cherries have 6 rows between them...for my output, these rows remain but are blank/#NA...any good ideas as to why this may be?!
Thanks any and all for your insights!
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Hi, i have a table with 50rows (drugs) and 30 columns (Citties) and sales qty of drugs are spread for each Citty. I need that this table of data returns in 3 columns named : City , Drugs,Sales Qty.
How can i do this ?
Br.Odi
Hello,
Please try to solve your task with the help of the Unpivot Table tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.
After you install the product, you will find Unpivot Table in the Transform section under the Ablebits Tools tab.
Hope this will help you with your task.
Hello,
I have multiple file for Raw data and one main file. like below. I need to know the last name of all EMP ID from all files to main file. How can i get this in single formula, by combined the all data in one file i can get that but its time taking. can i get this without combined the data in one file?
thanks in advance.
Below is the sample for data.
file 1.
EMP ID last name first name
101 yadav naveen
102 kumar deepak
103 patel gaurav
104 sharma vivek
105 Ghosh jay
File 2.
EMP ID last name first name
101 yadav naveen
200 kumar deepak
201 patel gaurav
203 sharma vivek
main file.
EMP ID last name
101 ?
102 ?
103 ?
104 ?
105 ?
108 ?
200 ?
201 ?
202 ?
203 ?
.
Hello,
Please try to solve your task with the help of the Combine Sheets tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.
After you install the product, you will find Combine Sheets in the Merge section under the Ablebits Data tab.
Hope this will help you with your task.
Dear Sir,
I Just Want to Transpose all related columns to a single Row.
Ex-
ICD QTY Invoice No. Vehicle No.
CR0103024080 106.00 SI/17-18/0011 RJ 06 GB 3195
CR0103024080 6.00 SI/17-18/0012 RJ 06 GB 3195
CR0103024080 8.00 SI/17-18/0013 RJ 06 GB 3196
CR0103024080 4.00 SI/17-18/0014 RJ 06 GB 3196
Result Required as
ICD QTY (Nos). Invoice No. Vehicle No. ICD QTY (Nos). Invoice No. Vehicle No. ICD QTY (Nos). Invoice No. Vehicle No. ICD QTY (Nos). Invoice No. Vehicle No.
CR0103024080 106.00 SI/17-18/0011 RJ 06 GB 3195 CR0103024080 6.00 SI/17-18/0012 RJ 06 GB 3195 CR0103024080 8.00 SI/17-18/0013 RJ 06 GB 3196 CR0103024080 4.00 SI/17-18/0014 RJ 06 GB 3196
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
iam preparing score card of students which includes semester 1 and semester 2 is there any formula to get the result in one cell by using vlookup formula or any other formulas???
For eg if i click semester 1 i need to get the result of semester 1 only.if i click semster 2 i need to get the result of semester two only in a single cell?? Can any one pls help me???
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Hi,
Your advice is appreciated as I am trying to fill in the ASSIGNED QTY in table1 from the table2 based on CUSTOMER NO. & ITEM.
table1:
ITEM CUSTOMER NO. ASSIGNED QTY
10001653 50023243 ???
10001656 50022603 ???
table2:
NO NAME 10001653 10001656
50023243 cust1 5 10
50022603 cust2 30 35
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
How use in vlookup in two sheets
Hi Anil,
To do Vlookup from a different Excel sheet, you simply supply the sheet's name followed by an exclamation mark in the table_array argument, e.g.
=VLOOKUP("text", Sheet2!A2:B15,2)
For more information, please see How to Vlookup from another worksheet.
If you want to perform sequential Vlookups in different sheets based on whether a previous Vlookup found the lookup value or not, you can nest Vlookup into IFERROR and use two or more such nested functions within one formula, like shown in this example: How to do sequential Vlookups in Excel.
I want to look through 15 tabs of a workbook and see in which of these tabs there is a value and depending on the which tab is the last tab that it was located at, insert to a cell a date. is this possible with a function or should I search for a VBA code?
How to get the smallest value in all occurrences of lookup value (duplicates)
Is there a way to parse a column, like column A for example that has 4000 names. On another tab there are 50 names
The first tab has 12 columns full of data but I only need 50 of those 4000 with the data
Instead of hand picking each one and copy paste
Is there a way to look at that source data, if name exist, add to "array" and then create a new tab with these 50 people and every column to the right?
i have a workbook, id number wise i entered their certificate expiry.
ex:
1:15/10/2016
2:15/10/2016
1:14/10/2017
2:14/10/2017
1:13/10/2018
2:13/10/2018
i want result ("what is the validity of 1(which means ans is 13/10/2018)).
how can find this result?