Email:      Pass:    Pass?
Advertisements


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

Leanne


Similar Excel Video Tutorials

Helpful Excel Macros

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
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e

Similar Topics







Hi All,

I have columns of numbers, 1-n, and they have random instances of repetition of each number. They are sorted in order. I need to total the number of instances of each number and feed that information into a graph.

For ex:
2
3
3
3
4
4
5

2 (1 instance)
3 (3 instances)
4 (2 instances)
5 (1 instance)

and then graph the instance frequencies. I have no idea how to accomplish this. Can someone help? I've searched quite a lot but haven't found the solution.

Thanks!


Hi,
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
Cheers
Jay


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,

Michael


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!


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?


I'd like to use vlookup, but there are multiple instances of each lookup_value. I want to use vlookup like normal to get the first instance of each lookup_value and paste these in one column. Then I'd like to remove these values from the table_array so that vlookup can find the second instance of the lookup_value and paste in the second column, and so on.

I've attached an example. I'd like to get the description data next to the class data column by matching by the ID column. The output should be (in columns C, D, and E):

one one' one''
two
three
four four'
five five'
six
seven
eight
nine
ten ten'

Thanks in advance for the help!


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.

~Gooser


I could just look this up, but I'll be lazy.
I want to name multiple instances of a value.


Ref.No - Date - Instance
123 01/01/2009 1st
123 06/01/2009 2nd
122 07/02/2009 1st


Column "Instance" to look at "Ref.No" and "Date", giving the result of "1st instance", etc. (Using dates order)

I've tried arrays of Match and count, but I'm stuck.


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

1.....F..........G..........H
2.....John.....1 Jan....15 Jan
3.....Joe.......3 Jan....11 Jan
4.....Jack.....4 Jan....24 Jan
5.....John.....15 Jan..22 Jan
6.....John.....???

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.


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.

1.....F..........G..........H
2.....John.....1 Jan....15 Jan
3.....Joe.......3 Jan....11 Jan
4.....Jack.....4 Jan....24 Jan
5.....John.....15 Jan..22 Jan
6.....John.....???

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.


Hello, this is my first post on this forum and I'm glad to be here!

I was wondering if someone could assist me with something, problem is that I'm not really sure how to explain it.

Column A includes a name
Column B includes an age
Column C states a division (this is the column that contains the Vlookup function)

Column G is where I have to put a range of ages (for instance, x=<0,>3)
Column H is where the division name is.

The idea behind this exercise is to use the Vlookup function in column C to reference column G, and return a value in column H.

Here a copy of my formula in column C: =VLOOKUP(B3,$G$3:$H$12,2,TRUE)

For instance, if Mike (A) is 16 (B) then he would be in the Midget division (C)

C is calculated by comparing column B to G, then returning a value in column H.

For some odd reason, I can't get column G to reference an age range, such as 1-5 years old.

Does anyone have any ideas? I've surprisingly never ran into this problem before.


Hi all -
I've actually written out a formula to do what I need but it's a beast, so I'm hoping someone can show me a simpler way.

In one worksheet I have a column of titles. In the next columns I have start date (1), end date (1), start date (2), end date (2), etc.
In a second worksheet I have a column of dates and a column of the titles. In the third column I need to return "In" or "Out" depending on the dates in the first worksheet.
If the date in the second worksheet falls inside the range of start&end 1, start&end 2, etc. then it should say "OUT". If it falls outside any of the ranges it should say "IN".

Because the names of the date columns are the same except for the number, I thought using fuzzy logic within the VLOOKUP/MATCH would work, but it still only returns the first instance (start date (1))

The formula I've written basically goes through every set of dates inside a very complicated IF statement. Is there a way to write out the formula so it looks across the whole array of dates before returning the value?

Here - if you're interested - is the formula i wrote:
=IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3

Can you get a VLookup to report the first value it encounters but then continue and report all other instances.
I am only aware Vlookup searches a range until it finds the first instance.
e.g.

A Apples
B Pears
C Grapes
D Oranges
A Insects
C Animals
A Bugs
B Blue

A vlookup of "A" would return Apples and I want it to return in column 1 Apples column 2 Insects and column 3 bugs.
The data is all mixed up and cannot be sorted so I could sort into blocks to change the lookup range.

I hope I am clear. Any help always appreciated.

A product number (this could be displayed 5 or six times in tab)
B


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,


I have this formula but I need it to find all instances and total up the result found in column 10 from each instance. Right now it just finds the first one and copies whats in column 10.

Here is the formula I have
=IF(ISERROR(VLOOKUP($A6,PREInvoice!$C$22:$L$42,10,0)),0,(VLOOKUP($A6,PREInvoice!$C$22:$L$42,10,0)))




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.

Thanks,

Glocker


I'm completely stumped on this.

i have a series of data points, that only has 5 values... (0, 1, 2, -1, -2). I'm trying to create a function that traces the instances of a binomial tree. so, the starting cell... a1, will start with 0, and from there, can take on values 1 or -1 (in cell A2). then from each of those.. 1 can go to 2, or the -1 can go to 0 (A3). once a cell reaches 2 or -2, the test ends. in column B, i want to have my formula that will, be conditional that 1 or -1 is first obtained. so, once a result other than 0 is reached, from the row the current cell, looking up from that cell.. so in b10, it looks through A1:10 for instances other than 0, if a 1 is obtained, then look for the first instance of 0 or 2. if a -1 is reached, look for a 0 or -2. then in b11, it would run the same test but in the range of a1:a11. also.. if in the cell b11, i want to look for any instance different from the previous. so, here is an example of what im hoping to do.
if column A is displaying
0,0,0,1,1,1,0,0,1,1,2... this should display 1, 2
another instance...
0,1,1,1,0,0,-1,-1,0,1,2 this should diplay 1, -1, 1, 2


any help would be greatly appreciated.

thank you in advance.


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?


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




Meat.xlsxMeat.xlsx


Hi,

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

Cheers

Lisa