Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

Leanne

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 need help on figuring out a formula to give me all possible results for one matching lookup instance.

Example

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?

Thanks!


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

Kyle


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..

Code:

=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 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?

Thanks,


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.

Thanks,

Glocker


Hi,

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.

Jason


Hello!

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.

Thanks.


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)

example:
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.

Thanks!


Hi,

Col O (which extends from O3 to O134) is populated by a formula that gives either TRUE or "" as an answer. In doing so, it creates random groups of TRUE answers down the column. A group could be one instance of TRUE followed by a random number of blank spaces until the next instance of TRUE. Or it could be 5 instances of TRUE, a blank space and then two more instances of TRUE followed by 3 blank spaces, etc.

My goal is to calculate the average members in each group. To do that, I need to have a count of the numbers in each group, I'm assuming via a formula in the adjacent Col P.

And it's that formula which has me stumped. Any suggestions would be appreciated.

Hi

I have stumbled upon a brick wall and can't progress any further. Basically, I have a sheet with two date columns. The first date column is entered manually and the second date column performs a vlookup from a separate sheet with two sets of dates. I want the result of the vlookup to be compared to the date in the static column one. If the date is earlier than column one I want to perform a second vlookup from the table and return the date in the next column. In a nutshell, the table of data that the vlookup is looking for, has an early date and a late date and depending on what static date is displayed on column one, sheet one, depicts whether an early or late date is returned into column two. I am probably making this out to sound way more complicated that it really is to somebody with Excel experience. I hope somebody can help me out on this one, as it's driving me nuts.

Many thanks,

Mac


Looking at the spreadsheet might make it self explanatory. I may be confusing the issue by trying to explain it, but here you go:

Column A and B have dates in 2 week intervals: A is the start date, B is the ending date. Column C has the pay date for that range. Each descending row has the next 2 week period with a new pay date in column C. I would like to put a date into the test date box and it find the row that the date would be between (including the beginning and ending dates in column A and B) and return in the Pay Date box the date in column C of the row.

I don't know what to put in a Vlookup formula to calculate between and including the dates in columns A and B.

Sorry for the sorry explanation. I hope the example helps.


I have an access table containing a column of excel objects. In the
simpliest case of this problem, if I doubled click one entry for excel, an
instance of Excel opens with the correct worksheet open. If I double-click a
second entry, a second instance of excel opens but instead of containing the
second worksheet, both instances contain both worksheets rather than each
instance containing a single worksheet. Further, if one of the instances is
closed(by clicking the x on the caption bar) both instances and both
worksheets are closed. I have tried messing with Window in toolbar and
ignore other app. but I cannot get the configuration I want which is multiple
instances, one worksheet per instance. The latter is way Word, Visio and
other apps work and to have excel do something different is a real problem.



I have a range say A1:B20. I can find the value of Bx for the First instance where Ax = 'string'.

However I have a case where I want to generate a list of all instances. While i appreciate its not good practice, in my case there will only be 2 or 3 instances, so it wouldn't be too cumbersome to have =[forumala to get instance 1] & ", " & [forumala to get instance 2] & ", " & [forumala to get instance 3].

My problem then is how do i find the second and 3rd instances?

Any pointers appreciated.

Regards

Nathan


Hi,

I am trying to write a formula which finds the last instance of a number greater than 30 in a column (B). The values are not sorted from smallest to largest as they correspond to a time series (A) which needs to be preserved. Ultimately, I want the formula to return the time at which this value occurs, but I think I can do that bit using INDEX

I have a formula to give the first instance (row number) of a number above 30, which seems to work, but beacuse I don't fully understand how it works I can't modify it to give the last instance. The formula is:

=MATCH(1,INDEX(--(B2:B883>=30),0),0)

Having only just learnt how to use MATCH and INDEX, I thought each should have 3 arguments, so I'm confused about how the 4 arguments here work. Also I can't find much on using logic functions within INDEX. Can someone explain this formula, and suggest how I might go about finding the last instance please?

I've attached the data if it helps.

Thanks


I have a VLOOKUP formula that, depending what is entered into B12, will return either a number or date. For example, if I select "Age" from my drop down list in B12, my VLOOKUP formula in H13 will return the date that I will turn the age that is entered into B13. IF I select "Date" from my drop down list in B12, my formula in H13 will return the age I will be on the date that is entered into B13. The formulas work fine, but once data has been entered as a date the cell remains formatted for a date. For instance, if at one point B12 contained a date, but now I want to enter an age, the cell is still formatted as a date. Is there any way around this? I have the same problem with the VLOOKUP result in H13 as well...


Hi,

I have a column of dates & I'm seeking to extract data that falls between Monday through Sunday (the work week).

I already managed to get figure out how to get the data.

Now, my questions is if the date hasn't arrived yet, the result is '0'. In these instances, I would like the result to be blank or "No Data Available", if the result is 0.

Beginning Day: 01/31
Ending Day: 02/06

Column A

Dates


No entries because there are no occurances.

What's a good VLookup or othe type of formula that find blank occurances between specified date ranges, then return a blank cell or "No Data" result?

Thanks




Hello. Some helpful people recommended the following formula for returning the last instance of a value in a column and returning the value to the right (like a vlookup kind of). They said that by changing the columns from $A1:A1 to $B1:B1 that it would select the instance right before the last instance. I've tried this and it doesn't work. What am I doing wrong? This is an array formula btw. Thanks.

=INDEX('05-13'!G:G,LARGE(IF('05-13'!$F$1:$F$2885=A44,ROW($F$1:$F$2885)),COLUMNS($A6:A6)))/1000

In one column I have unique numerical IDs which could be duplicated up to three times. In another column I have the inputs "Yes" and "No" which correspond randomly to these IDs.

In a third column I would like to return an affirmative output for the first instance of "Yes" for each unique ID code.

For instance:

Column A

23
23
23
4
4
6

Column B

No
No
Yes
Yes
Yes
No

Desired Column C

0
0
1
1
0
0


Hi all,

I have a question that doesn't seem to have been addressed on this board before (or Google).

Windows XP, Excel 2003.


I have a workbook with multiple worksheets.

Each worksheet is labeled with a unique ID (alpha-numeric).

Each worksheet contains rows of text in no particular order, and of random size.

I want to extract all text on each worksheet that falls between the first instance of a text string and the first instance of another text string.

So for instance, on one worksheet in column A:

This is the header
lots of text to follow
blank
More text
Section 1:
first line of text I need.
second line of text I need.
third line of text I need.
Section 2:
Blah
Blah again



I want to extract all the text between the rows "Section 1" and "Section 2".
I also want to make sure I extract the whole row, and not just column A.

There can be a random number of lines before and after the block of text I need to keep. And The Block of text I want might also have a different number of rows itself in each worksheet.

There can be multiple instances of "Section 1" and "Section 2" as you move down the column of data, but I just want the first group of data (and ignore anything after that).

I then want this data moved to another clean worksheet, along with the worksheet name beside the data. So for worksheets labeled "AA11" and "BB22" the output on a blank worksheet should be:

AA11 BLOCK OF TEXT
BLOCK OF TEXT
BLOCK OF TEXT

BB22 BLOCK OF TEXT
BLOCK OF TEXT
BLOCK OF TEXT


At first I thought to use the MATCH command to find the row numbers of the first instances of "Section 1" and "Section 2". Then I could reference those rows from the blank worksheet. But it only works if it is sorted.

The block of data I need cannot be scrambled. It needs to stay in the same order it was originally grouped by.


Since the size of worthless data is random, I can't just delete to row # on each worksheet (either down from the top or up from the bottom).


Help???

Thanks for your time!!!


Hey all

I am using this formula

Code:

=VLOOKUP($F4,$A$4:$D$11,COLUMNS($A$1:B$1),0)


to extract data from a table, however, the cust id in F4 is also repeated further down the table with different values in B;D.

This type of lookup only returns the first instance.

Can anyone point me into the right direction of a formula which will pick up multiple instances. I've got a feeling that its an if rows countif small index match type one!

please see the attached


Cheers fellas


Hi,

I have a spreadsheet that tracks staff sickness.

Column A = Staff Name
Column B = Date of sickness

Each day of sickness is included within the spreadsheet (so a record per day). From this I can count the number of days sickness per person. I now need to be able to count the number of instances of sickness so some how recognise that 1st June, 2nd June, 3rd June = 1 instance of sickness as the days follow on from each other. I would also need it to recognise that someone sick on a friday and then the following monday would count as 1 instance (so somehow include weekend dates even though they are not entered in to the spreadsheet).

If anyone can point me in the right direction of how to do this I would be so thankful :O)


In a worksheet, I have the following columns:

A: Name
B: Absence Type
D: Date
F: Event

"A" is the employee name. "B" explains the type of absence, (SICK, LATE, UNAUTHORISED, UNPLANNED). "F" states if the absence is the first date of the instance (TRUE) or a subsequent day of the same instance (FALSE).

In order to effectively monitor absence levels, I'm looking to calculate a rolling % of each absence type.

I will require a column "L" that tallies the number of total number of "TRUE" instances for each absence type, per person, within the last 12 months. A column "M" that tallies the total number of instances of an absence type, per person, within the last 12 months. And finally, based on column "M", the % of that absence type within the last 12 months.

Regarding that final column, I have been advised that to obtain this value the basis of the calculation would be "(Number of Instances / 220) * 100". I have been told 220 represents the approximate number of days an employee should work over a twelve month period, but I am dubious about the accuracy of this calculation and any alterntives would be appreciated.

Many thanks.




Hi, I need a formula that counts individual instances of times and dates within a list and displays a count number next to them.

Example

Date Time Instance

08/08/14 10:00 1
08/08/14 10:00 2
08/08/14 10:00 3
08/08/14 10:00 4
08/08/14 11:00 1
08/08/14 11:00 2
08/08/14 12:00 1
08/08/14 12:00 2
08/08/14 12:00 3
08/08/14 13:00 1
08/08/14 13:00 2

Thanks in advance.

JD