Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Returning 2nd And 3rd Instances In Vlookup

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi all,

I know this is possible but not sure exactly how I'd do it. I have 250 rows of data and in column A I have various unsorted dates. There will often be 2 or 3 instances of the same date in this column (but never more than 3) and are in random places in the column (not sorted). I need a vlookup formula which can return the value in column B for the 1st instance, the 2nd instance, and the 3rd instance of each date. I don't want to use a VBA code for it and I also can't sort the data by the date in column A. Does anybody know the formula I'd use for this?

Many thanks


View Answers     

Similar Excel Tutorials

How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Vlookup with a Range of Numbers in Excel
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
Vlookup on Data with Spaces
How to use the Vlookup function when your data has extra spaces in it. This happens when the lookup data is import ...

Helpful Excel Macros

Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a

Similar Topics

I have a list of data in sheet 1 in columns A and B. The data in column A is has 5 different categories (for now say Cat1; Cat2; Cat3; Cat4; Cat5) and these are random and mixed all the way down the column. Column B has individual data next to the column A data.

I'm trying to get a formula in sheet 2 (row 1) that reads down sheet 1 column A, picks out the 1st instance of a specified category (say Cat1) and return the value in column B. And when I drag the formula down in sheet 2, I want the next cell to return the value in column B of the second instance of Cat1, the 3rd row to record the 3rd instance of Cat1 etc, regardless of where these instances are in sheet 1 (remember the data is random and mixed).

Is there a formula for this (i have a good reason for not using the sort facility in excel)

Thanks in advance

Usnure of how simple or hard this is but I cannot solve it !!!

I have a list of data that I don't want to use VBA on - could do it myself otherwise... I cannot create a pivot table and cannot sort it either, it has to stay in the order it is in.

I am looking for a formula that will display the row number for each instance of a number given column A. For instance there is several instances of number 14 and can be in any order of the range of row 1 to row 5000. Preferably would be good to have a list of cells all under each other showing the row numbers that they appear.

I know vlookup will return the first instance and only the first and that lookup will return the last instance. I can use Index and match but only basically.

Any help would be appreciated; Is this acheivable?

Many thanks,


I need help on figuring out a formula to give me all possible results for one matching lookup instance.


Data Sheet:

Phone # DEPT 422-8888 Finance 422-8888 Sales 422-8888 HR 422-8889 Finance 422-8889 Sales 422-8889 HR

I need to lookup based off of the Column A, and return all instances possible from Column B. I started with a vlookup, but I know that only returns that first match this is discovered. Any suggestions on how to have all instances returned, or even just a way to denote if there were more than one instance of the # repeating?


here is what I put in the cell originally.
=IF(ISNA(VLOOKUP(DATE($A78,COLUMN(B77)-COLUMN($B77)+1,DAY(EOMONTH(DATE($A78,COLUMN(B77)-COLUMN($B77)+1,1),0))),'Data Streams'!$CC$1:$CI$1000,4,FALSE)),"",VLOOKUP(DATE($A78,COLUMN(B77)-COLUMN($B77)+1,DAY(EOMONTH(DATE($A78,COLUMN(B77)-COLUMN($B77)+1,1),0))),'Data Streams'!$CC$1:$CI$1000,4,FALSE))

OK, this formula, as unwieldy as it is, WORKS. Here's my problem: If a76 is blank, I want it to return "". So I put in this modification (changes are bold):

=if($a76="","",IF(ISNA(VLOOKUP(DATE($A78,COLUMN(B77)-COLUMN($B77)+1,DAY(EOMONTH(DATE($A78,COLUMN(B77)-COLUMN($B77)+1,1),0))),'Data Streams'!$CC$1:$CI$1000,4,FALSE)),"",VLOOKUP(DATE($A78,COLUMN(B77)-COLUMN($B77)+1,DAY(EOMONTH(DATE($A78,COLUMN(B77)-COLUMN($B77)+1,1),0))),'Data Streams'!$CC$1:$CI$1000,4,FALSE)))

That returns, "The formula you typed contains an error", and highlights the third instance of the word "COLUMN' in the formula. I'm banging my head here - where did I goof?

Here's what I am basically trying to do:

There is a list of data like this:

Ticker Date
goog 1/2/07
goog 1/3/07
goog 1/4/07
goog 1/5/07
goog 1/8/07
goog 1/9/07
ibm 1/2/07
ibm 1/3/07
ibm 1/4/07
ibm 1/5/07

Basically I need is VBA code to do the following:

Identify the first instance of goog, give me the the row#
Identify the last instance of goog, give me the row#

Identify the first instance of ibm, give me the the row#
Identify the last instance of ibm, give me the row#

I guess what is getting complex for me is the following:
There will not always be two unique stocks, sometimes it could be 2, sometimes it could be 10, or whatever, so do I need to build an array of some sort to get all the unique values in column A (ticker column) and then do some search for first and last instances.

Also, from the example, there won't necessarily be the same number of instances of each stock either.

I hope this makes sense and any help would be much appreciated.


Ok so im trying to make something that is easy to update, which will be read off a big table through a dump of data.

I am graphing headcounts by date for each day of the week. It's generally a 365 period that im dealing with here so im trying to make it so that you dump the data which is fed into tables that search for each date that corresponds to a specific day and reads the count from there.

The data comes as follows

Column A - Day of Week
Column B - Date
Column C to Z - Head counts by time

So I need something that searches column A for all instances of "Monday" for example and returns each corresponding date. The rest I can take care of but it's being able to retrieve each instance without having to sort the data and manually pull them out that im trying to avoid.

Im not sure if this is even possible :/ Any advice would be greatly appreciated


Alright, so hopefully this isn't as complicated as I think it is but here is my dilemma. I'm doing a sumif for dates. If there is a date that equals a date in my range then I'm adding the hours entered in for that date. However, with the data I'm importing there are multiple entries and only one date. Is it possible to include in my formula a variable reference for this purpose?

Here is the code I'm using..


=SUMIF('Report Data'!$D$3:$D$40000,A1,OFFSET('Report Data'!$Q$3:$Q$40000,1,0,1,1))

In column D is where the dates are at. A1 is a date I want it to look for in D3:D40000 if it finds the date I want it to add the hours for that date it finds. I had to use offset because the hours entered are for a row below the date in column Q.

Now the problem Is there can be sometimes up to 10 instances entered in for a date in column D. How would I go about writing that into the code so it adds the hours in column Q for just 1 date?

Here is a sample of the workbook:

I have a spreadsheet tracking work assignments for 7 employees. Column F has their names, column G has the date they begin an assignment, and column H has the date they finish. I want a formula for column G that will look at the name in column F, find the last instance of that name, and put the finishing date from column H into this new instance of the name, in column G.

2.....John.....1 Jan....15 Jan
3.....Joe.......3 Jan....11 Jan
4.....Jack.....4 Jan....24 Jan
5.....John.....15 Jan..22 Jan

I want G6 to find the last time that John appeared in column F, which happens to be line 5, and automatically take the date from H5 and put it in G6.

Hi all

My data shows the type of work being undertaken during a particular time period during the day. So for example, X X Y X X Z X Y spread over 8 columns would mean that the person is working for 8 hours, the first two hours are doing task X, the third is task Y, etc...

I am looking for a formula to return the column number of the first and last instance of a particular task type. I have used the MATCH function, with the final parameter of "0" to return the first instance of each task. That works fine. However, how can I return the column number of the last instance of a particular task type? So in the example above, I would like to return column 7 as the last instance of task X, or column 8 as the last instance of task Y.

Thanks in advance.

I need help transposing data. Suppose I had a unique identifier (e.g., SS#) in column A and a date related to a login instance in Column B. When sorted, I might have 2 logins for the first SS# and 5 logins for the next. I would like my spreadsheet to have one one ss# in row 1 and all the login instances in subsequent columns along that row. I need some sort of transpose function that will transpose the login dates at each change in Column A. Any suggestions?


So I'm basically doing a big data management project, and to begin I need to get data from one sheet to another sheet in a somewhat specific format. I have 2 pages, each with a list of dates. The "data" worksheet has a column of dates next to a column of corresponding identifier numbers, some of the dates are repeated but the identifiers are all unique. The second worksheet, "analysis", has two columns of dates (which are not necessarily the same dates that are on the data sheet), next to two columns in which I'm trying to use VLOOKUP to find the identifiers from the dates in the "data" worksheet. The first column of VLOOKUP's needs to find the identifier for either the first instance of each date it is referencing, or for the closest following date. The second column's VLOOKUP's needs to find the identifier for either the last instance of each date it is calling, or for the closest preceding date.

Here are some screenshots of what I'm working with.

excel help 2.PNG - Data worksheet

excel help 3.PNG - Analysis worksheet

Any suggestions for how to do this? Is there a VBA script that can be used to do this more easily than a complicated series of conditionals and VLOOKUP functions?

Thanks a lot!

Someone please help me: Situation is that I'm performing a vlookup function where there is more than one instance of the lookup value. The formula returns the first instance of the value only, I want to sum the results of all instances of this value - any pointers appreciated.

New to the forum and need some help with a macro.

On a given sheet I have a column starting with E1 that has 1000's of numbers which contain some equal values. Some of the values 251452 for example in column E have dates in the same row on another column, 11-05-2009 in A1 for example.

I need a way to find all instances of 251452 in column E then capture the date in column A on the same row if it exists for any instance of 251452 and paste that date to column A for those rows of 251452 that dont have a date.

There are many different values in E and different dates so I need it to repeat over and over down the E column. There may be some values in column E that have no dates at all so those need to skip if no values in E have no dates in column A.

This sounds easy but it has been a few years since I have worked with macros and I would appreciate any help.



I have a range of cells A1:AF33, each one can contain one of 35 different text entries each of which corresponds to a random value. These entries can vary depending upon other circumstances.

The problem I am having is that there are multiple instances of the same text entry but I need each instance to calculate its own random value.

For example,

Cells C9, W15, AA21 all contain AF which equates to randbetween(60,120) - however each instance needs to generate its own random value.

I tried using a vlookup with formula for each text entry but realised that each instance was generating the same value as they were looking at the same formula.

Any ideas on how I can do this?


I'm looking for a formula that will return the column header (a date) of the first instance of a number greater than 0.

For example if columns A through S had dates as in row 1 (header row), and in row 2, every column contained a 0 up until column P, I would want the date (Row 1, Column P) returned? I think this might be a sort of index/match formula, but I am not very familiar with these.

Appreciate your help.




Its been years since I have had to use and Vlookup function, and I'm afraid I can't seem to get this right. Been butting my head against a brick wall for a while now.

I am trying to find the number of items ordered for each product based upon the date. I have got the formula returning the first values correctly, however this may just be a fluke as the formula carried down does not work.

The Orders Spreadsheet holds the date ordered, the unique identifier for the product and the number of items ordered

The Master spreadsheet wants to collate that information into a running table. Column C = first order date, Column D = second order date etc.

I've checked the Vlookup I'm using, and it is returning the first instance of the product id, it is not making a match based upon my 'If' statement which is the date qualifier.

How can I change it so that my formula returns the right data for the date, and the right data for the product id

Spreadsheet attached - and thanking you in advance



I could really use someone's help with this. The problem with vlookup is that it returns the corresponding value in the first instance the formula finds the lookup value in the referenced column. I can't figure out a formula that would consider three (four) columns, A(Name), B(Key), C(Type). A is the ultimate lookup value, then if C contains a specific value, then return value in B. Column B is the most unique (never duplicate values).

Basically, this is the gist of what I want:
Look up "Andrew" in A, then see if "Andrew" in A has value "Economics" in C, if so, then return value in B.

To take this a bit further: Return all corresponding values in B for every instance in which "Andrew" in A has a corresponding value "Economics" in C. Concatenate these B values.

If it can't get any more complicated, I'd would like to concatenated the corresponding dates (Column D-Key Date). I figured out how to do this perfectly, but I am not sure how the dynamics/formula will reflect based on the above formulas for what I want to achieve.

Corresponding Dates of each Key to be concatenated and placed within parenthesis.

Below is the formula I've used until I realized the flaw as mentioned at the top.

=IF(VLOOKUP($F14,$A$3:$C$10,3,FALSE) ="Architecture" ,VLOOKUP($F14,$A$3:$B$10,2,FALSE),"")&" "&IF(NOT(VLOOKUP($F14,$A$3:$C$10,3,FALSE) ="Architecture" ),"",IF(ISBLANK(VLOOKUP($F14,$A$3:$D$10,4,FALSE)),"(Not Submitted)","("&TEXT(VLOOKUP($F14,$A$3:$D$10,4,FALSE),"m/dd/yy")&")"))

I should say that that parts in red doesn't work, hence the post.

If you can figure out a way to solve this, then I'd be most grateful. If you can also explain what I am doing wrong, then please let me know because I am still learning Excel.

Thanks in advance,

I swear I've done this before, but I'm drawing a complete blank. Is there a formula that will allow me to sum values from one column based on corresponding text in another column? Example: column A lists the stores I buy from and column B lists how much I spend each time I go to the various stores. Entries are by date, so column A is unsorted. I want to build a summary table to the side that will find every instance of a given store name in column A, then sum the corresponding amounts in column B. The entries cannot be sorted; they must remain in date order. If I need to clarify my example, please let me know what other info will help. Thanks in advance!


In column A i have a list of scores, and in column B i have the date associated with each score.

I want to display the overall highest score, overall lowest score, and the most recent date associated with each. I can get the highest and lowest easily, but when i do the VLOOKUP for the dates, i am always returned with the earliest date, not the most recent.

So, for instance, if the top score was 68 and i achieved that on 3rd Jan 2008, 5 May 2008 and 6th July 2008, i would want the formula to return 6th July 2008. Same for lowest.

I need to keep the data in date order, is there a way to get VLOOKUP to provide the most recent date? Or do i have to get a bit more complex?

Thanks in advance!

Column A includes 2 instances of the same text string.

I am looking for the function (or array formula, if necessary) that will return the cell address where the first instance (from top to bottom) occurs.

Same for the 2nd/last instance.

For future reference, what is the function (or array formula) for the nth instance?

Forgive me if this has been answered; I have searched without success.


Hello, I have two worksheets. In the first sheet I have a list of numbers (750 rows) in the second worksheeet I have data in column 6 that might match the numbers in Sheet 1. I need to compare all of the numbers in Column 6 to Sheet1 and if there is a match I need it to put the number 1 in column 1.
For example, on sheet 1 is the number 1234. On Sheet 2 in Column 6 there are three instances of 1234. For these three instances, I need to insert the number 1 into column 1 for the row of each instance.
Any help is greatly appreciated.

OK. Here is my predicament:
I run a macro which copies and pastes data from one sheet into a master sheet. The master sheet contains a 'Date' column. (Not necessarily todays date). The original sheet does not have a date column but contains the required date in the filename.
What I need to include in this macro is some code which will extract date from title of original spreadsheet and paste date into Date column of master sheet. The date needs to be pasted next to every new instance of data.

Does this make ANY sense? I have the formula for extracting the date. My problem is filling in the date column for every new instance of data in the master sheet and converting all of the above into macro code!

I have an interesting problem trying to sort data in excel.

I have a spreadsheet which has 120000 odd cells in 20 or so columns and i only need info from 4

cal a =job
col b =job name
col s = trade
col v = value

my problem is each of the columns has multiple instances of the same detail e.g col a has 5 instances of job1 as has column b column s has 2 labour 1 misc and 1 materials col v has 2 labour values 1 misc value 1 materials value so I need to create another workbook with

col a job no
col b job name
col c trade 1
col d trade 2
col e trade 3

the info for col a and b I have sorted so that they only have 1 instance of each per row but I dont know how to get the other info in the right place
I now need to populate c1 to e500 with the value per trade per job.

I thought about vlookup or sumif but cant quite get it right

any help would be greatly appreciated


I have a file that I need to use vlookup to return certain dates, but I don't know how to combine If, vlookup, match index etc.

I don't even know if a cell formula will be able to return the data I want, or I have to create a code to do this.

My situation is:
I have a date in column A3(manually entered)
and I want this to be my criteria in returning my data(which is in a separate tab)

A3 = 28-May-10

R16 should be the previous date on the data tab(which in this case is 14-May-10)

AH16 should be April 16
which is the 1 month prior date on file
(although there are 3 dates with April, it will return the oldest date)

AX16 should be March 8
which is the 2 months prior date on file
(although there are 3 dates with March, it will return the oldest date)

Attached is my sample file for a better understanding.


I need to create a chart that looks at the log date of an issue (Column J) with all dates one week from the current date. There are 4 different possible values for each order. For instance, an order in Column J can have a missing components, manufacturing quality, service, or freight issue.

Here's the set up:

-Column J is "Full Log Date" (this is the date range. I need to have it show every order within one week of the current day)

-Column U is "Instances of Missing Components". I need the chart to sum this value for the dates in the one week range.

-Column X is "Instances of Manufacturing Quality". I need the chart to sum this value for the dates in the one week range.

-Column AJ is "Instances of Service". I need the chart to sum this value for the dates in the one week range.
-Column AN is "Total Freight Issues". I need the chart to sum this value for the dates in the one week range.

Any help is appreciated. Thanks!