
Returning 2nd And 3rd Instances In Vlookup


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Returning 2nd And 3rd Instances In Vlookup  Excel

View Answers


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
Return First NonBlank (Array Formula)
 Array formula for returning cell content from first nonblank cell in a row that uses INDEX, MATCH and the not comparative operator and the symbol for ...
Return Cell Address of First NonBlank
 Array Formula for returning cell address for first nonblank cell that uses the functions: ADDRESS, MIN, IF, COLUMN, ROW and the not comparative opera ...
VLOOKUP 11 Unusual Examples
 See these 11 VLOOKUP tricks: 1.VLOOKUP algorithm 2.VLOOKUP, Named Ranges, Exact Match, COLUMNS function& Data Validation List 3.Com ...
VLOOKUP Categories
 See when to use IF and when to use VLOOKUP depending on your category setup. See when the VLOOKUP function beats the IF function, but only if categori ...
Similar Topics
Hi All,
I have columns of numbers, 1n, 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
4228888
Finance
4228888
Sales
4228888
HR
4228889
Finance
4228889
Sales
4228889
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.
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 15 years old.
Does anyone have any ideas? I've surprisingly never ran into this problem before.
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.
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
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,
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 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)))
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, 11052009 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
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.
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
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!
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!

