Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Vlookup Multiple Rows With The Same Lookup Value.

I'm trying to get a result of all rows in a table that has the same lookup value.

For example:


I want to look for David and that the result will be all rows that starts with David:

David 25
David 68
David 902

I tried using vlookup but it always returns the first row.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Multiple Ccolums/rows To Get Data From Multiple Columns/rows (vlookup)
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............

View Replies!   View Related
Vlookup- Multiple Conditions For Lookup Value
MetricsCategoryJan 2008VisitsTravel50view_offerTravel10

Above is the data I want to lookup on. I need to be able to do a lookup for travel visits and a seperate lookup for travel view_offers.

Is there someway to do this with a vlookup or maybe match index?

View Replies!   View Related
Using VLOOKUP To Retrieve Multiple Data For The Lookup Value
I have to write some VLOOKUP formulas to pull several items from a report, the problem is there could be multiple items for each lookup value as the example below:

Company Data ABC Monday ABC Friday ABC Thursday DEF Friday GHI Monday
I need to reference every piece of data for every company but using the VLOOKUP formula, I am obviously only able to get the first instance of the lookup value (company ABC)

How to I write the formula to lookup the other data? The practical appliation I am using this for has anywhere from 300 to 400 lookup values.

View Replies!   View Related
VLOOKUP To Return Multiple Cells Of Same Lookup Value
I am trying to create an automated receipt for a list of artists. I have given the artist an individual reference so I am doing a VLOOKUP to return description/price value etc but I can only get it to return the initial value and I have approx 10-15 works under each reference. Maybe VLOOKUP is the wrong formula to use.. I have attached the file.

View Replies!   View Related
Vlookup Table To Lookup Data In Columns And Rows
I have a lookup table which needs to be able to retrieve data from columns as well as rows and not sure if this is possible.

My data is set up with an ID number, financial year and data across the columns e.g

ID F/Y Apples Pears
9999 2004/5 5 4
9999 2005/6 7 8
9881 2003/4 5 3

My lookup table will have the ID number field which will be manually entered to lookup the information on the data sheet. e.g

ID 9999
FY Apples Pears
2004/5 5 4
2005/6 7 8

I can't work out a way to get excel to only give me the number of apples and pears for that year for that ID number. Not all ID numbers will have the same number of financial years so I may need to use VBA.

View Replies!   View Related
Lookup With Multiple Rows.
I have been trying to auto update a table by looking up information from a table on top. However, for the top table there are multiple entries in the same column, and my look up only checks the first row and ignores the rest.

My formula at this point is: =IF(ISERROR(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))=TRUE, "", IF(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4)=0, "", INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))).

If you can take a look at my attachment, this formula is in the cells I40:W40. Eventually, I will want to populate the entire bottom table with the same formula.

View Replies!   View Related
Possible Multiple Rows LOOKUP
I have a worksheet which Sorted in ColA contains Product Id#, ColB contains Vendor, ColC contains Grade# (1 thru 6). Now there may be multiple Vendors (ColB) for same Product # (ColA) but with different Grade# ColC).

I would hope if you can please help me formulate for ColD to pull (list) ALL Vendors for the same Product# (there maybe as many as 6) PROVIDING that the Grade# (ColC) is either 3 or 4.

View Replies!   View Related
Using Vlookup On Multiple Rows
i am trying to create an interactive calendar that displays what is happening on different dates depending on the activity. i.e. if 'workshops' is selected then it will show all the workshops happening in the month. the problem is that i have created a new line for each workshop in my table array and want it to display all the returns in a single cell on the calendar. I am using a vlookup and have used a unique reference so that it picks up multiple rows, but it will still only return one value.

is this possible or do i need to collate all the data into one cell? I have attached the spreadsheet as i am sure the above prob does not make sense.

View Replies!   View Related
Vlookup - Multiple Rows To One
Just learned a little about vlookup and wondering if it can be use to solve the following. I have attached "worksheet1" that shows what I'm trying to do. The "detail" sheet is downloaded from another software package. There may be one to five rows of Product #'s with their component # and percentage on the same row. I would like (on a new sheet) put one Product # and all the corresponding component #'s and percentage's on the same row. (An example of how I would like it to be is on the "one row" sheet.)

View Replies!   View Related
Lookup A Value Based On An Array Spanning Multiple Rows And Columns
I'm having difficulty finding a way to ask excel to lookup a value based on an array spanning multiple rows and columns. There's a mock example in the attached.

View Replies!   View Related
Fastest Lookup Method: Use A Key To Lookup A Value (VLookup, Index/Match, DGet, And The Rest)
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: Does Excel/VBA have a way to create a branch table for such lookups?

View Replies!   View Related
Lookup Vs Vlookup
i am using Vlookup in all My files and the 4Th argument i put it 0 to give exazct result

My Question
Lookup Function give the Exact or approximate match and as i know Vlookup give the first result but Lookup give the last result

View Replies!   View Related
Lookup Multiple Same Value And Return Multiple Corresponding Value In Ascending Order
I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean.

What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E.

Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.

View Replies!   View Related
Multiple Parameter Lookup For Multiple Table Ranges
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them

View Replies!   View Related
VLOOKUP() Style But 3 Value Lookup
I have a table that has, amongst many columns, a year, month and a name column.

I want to do a formula that takes in 3 values (name, year, month) and returns a sum of values found in a variety of other columnswhere these 3 values are matched.

I would know how to do this in SQL but wonder if Excel can do this type of thing on one large data table?

View Replies!   View Related
Two Lookup Values, Vlookup
I want to match two different values in the same row and then have it return another value in that row.

View Replies!   View Related
Vlookup Using Two Lookup Values
Is there a way to perform a vlookup with two lookup values?

I'd like to look up the value of A1 and B1 in a table with A1 and B1 values
included to return cell C2 from the table. I've had limited
succcess using concatenate and then using vlookup on that cell but i'd like
to not have to do the concatenate step.

View Replies!   View Related
VLOOKUP With The Lookup Value On A Different Sheet
Formula is on Sheet1 and table array is on Sheet1
but the lookup value is on Sheet2 in Cell B15

Below does not work, either does anything I have tried.


View Replies!   View Related
Vlookup: Lookup The Title
I have a tab with the following on:


I then have another tab which is similiar to this:

Australia Danny 23
Australia David 25
Canada Ben 35

Basically what I want is....where ive put some formula I want to lookup the title, in this case australia which is the cell above, search within the table then enter the name to the right of that. I have done this, however it only ever uses the first name on the list..... Example i always get danny on a role, i have managed then to get it to say david underneath but then if i copy the forumla down again it continues with danny, david etc.

View Replies!   View Related
Lookup Second Occurrence With Vlookup
I am using vlookup to find the ORIGIN data and DESTINATION data but the subheading is the same for both- see below(site name, city...). How do I find the second occurrence for the same heading?

Is there another command that would be better?

col A col B
Line#1 CITY

Line#1 CITY

View Replies!   View Related
Vlookup With Two Lookup Values
=VLOOKUP(C29,'Zero Days YTD'!B:Q,16,FALSE) is my current formula where C29 = Jack Bates.
As of this month Jack Bates is now in two places on worksheet Zero Days YTD. One is named "Jack Bates (from AMU 3/1/07)" and the other named "Jack Bates (to APU 3/1/07)"

I tried =VLOOKUP("Jack Bates (from AMU 3/1/07)"&"Jack Bates (to APU 3/1/07)",'Zero Days YTD'!B:Q,16,FALSE), but that didn't work.

View Replies!   View Related
Vlookup And Offset: The Figure Of 10 Rows Across And Then 2 Rows Down From That Point
I have done a search and I think this is possible but just can't convert any of the examples to work for me.

This is the calcuilation I would normally enter -


However I don't want the cell 10 rows across when a match is found I want the figure 10 rows across and then 2 rows down from that point. i.e. if the match is in row A9 I want to return the figure in J11.

View Replies!   View Related
VLOOKUP-lookup Value Has Been Both TRIMmed And VALUEd
The formula I'm using is: =VLOOKUP($B724,Sheet1!$A$2:$S$17120,6,FALSE)

The B column in the lookup value has been both TRIMmed and VALUEd. The column that would contain the matches in Sheet1 has also been TRIMmed and VALUEd. I've confirmed that if I manually search for my value in Sheet1 then I find the data I'm looking for, but I always get #NA when this is run.

I have successfully done other VLOOKUPs between different columns in these worksheets, but can't get this one working. Excel version is 2003 SP2 if that makes a difference.

View Replies!   View Related
Vlookup Returns #N/A When Lookup Value Is Not Found
This formula works perfectly except when worksheet 2 has a code that is not on worksheet 1, then I get the error #N/A. I understand why I get the error, but not how to fix it. Obviously some sort of nesting - but what and how?

What I want on worksheet 2, column I - if there is a code on worksheet 2 but not on worksheet 1 - a return of "N/A"...

View Replies!   View Related
Vlookup Errors (#N/A!) When Lookup Value Is Present
After dumping the Showcase Query result into an Excel file I am using a vlookup to insert data. My problem is that the vlookup won't find results unless I actually click on the cell that contains the lookup value to activate it. I have tried formatting the cells (both lookup and the return value) as general and text and nothing works (which makes my macro usless if it requires user intervention).

View Replies!   View Related
Multiple Column Lookup Across Multiple Sheets
After going through multiple threads in the forums, I got this code to do a multiple VLOOKUP method.


It works perfect on a sample sheet. But when im trying to implement it in a sheet with too much data, it always fails.

I have attached the sheet I am trying the formula on. I have grayed down the columns which needs formula's. The data is picked out from the second sheet.

This is how I have modified the formula to suit me..

=INDEX(Data!$G$2:$G$663,MATCH(1,(Data!$A$2:$A$663=$C2)*(LEFT(TEXT(Data!$E$2:$E$663,"mm/dd/yy hh:mm:ss"),8)=$I2),0))

<<Please note that all the dates and numbers in the sheet are in "text" format for ease of use>>

View Replies!   View Related
Vlookup Where The Lookup Value Isn't In The First Column Of A Range/table
I would like to do a vlookup where the lookup value isn't in the first column of a range/table. Would I use index/match? For example, I have fields for vendor, part # and location in that order. I'd like to pull up location with a formula based on part #.

View Replies!   View Related
#NA Error In A Vlookup When Using SEARCH And LEFT As The Lookup Value
In the attached sheet I am trying to use the formula below but am getting a #NA error. I have narrowed the problem down to the use of the SEARCH and LEFT functions that I am using to determine the lookup value of the VLOOKUP formula.

what I am doing wrong? If I substitute the SEARCH and LEFT function with the number "14" it works just fine. You can find examples of both in cells B29 and C29 on the rename tab.

View Replies!   View Related
Vlookup:keep Cell Reference As Lookup Criteria
See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.

View Replies!   View Related
VLOOKUP Modification: Modify The Lookup Array Key
it's possible to set a "key" for a vlookup so that one of the parameters of that key can be any value? For example if the data set key used in the lookup contains a concatenation of 4 parameters (a_b_c_d), is there anyway to modify the lookup array key so that the "c" parameter can be anything, ie. something like a_b_*_d ?

View Replies!   View Related
Macro To Add VLOOKUP Formula To Lookup Another Workbook
I'm running against this problem: a file should be run whenever people want.
however, the last part to complete the file is that the file from yesterday should be opened.

From this file data will be extracted via the VLookup formula...that's the plan.

However, with dim statements it won't work.
for now the code is:

Dim mynum, mysearch, this1, that1, other1

'Open the file from yesterday. Define the date string of this week and last week
mysearch = Left(mynum, 8) & Right(mynum, 2) - 1 'mynum is a date string like 2008-06-26
this1 = "Outstanding invoices " & mynum & ".xls"
that1 = "Outstanding invoices " & mysearch & ".xls"
Set other1 = Workbooks(that1). Sheets("Raw"). Range("comments")
ChDir "Z: FINANCEF&AAPInvoices overdue"
Workbooks.Open Filename:= _
"Z:FINANCEF&AAPInvoices overdue" & that1

the "this1, that1" etc appear when you point with your mouse in the code as correct but
the VLookup function won't work: it takes a long time and when interupted the macro the cells show the VLookup formula with "[that1]" instead of the workbook's name.
I also tried to dim that1 as workbook but that did not help.

View Replies!   View Related
Prevent VLOOKUP Until Lookup Cell Has Data
On the uploaded workbook, Daily Nutrition Worksheet. How do I clear all the data to start fresh without deleting all the formula?

View Replies!   View Related
Move Lookup Table To Same Sheet As VLOOKUP
One other common problem is storing the Lookup Function on another sheet to the Table. On The Uploaded Workbook. Is it possible to change the Vlookup to the same worksheet as the lookup list to remove the disadvantage of having them on separate worksheets. If I have misunderstood the statement in the quote above.

View Replies!   View Related
Vlookup Formula Changing Lookup Range Automatically
As I copy and past my lookup formula down the page it is changing the lookup range which I think is what is giving me so many #N/A results. My first Formula is
=IFERROR(VLOOKUP(A2,Coors2!A2:D3765,3,FALSE),IFERROR(VLOOKUP(A2,'AB2'!A2:C13944,3,FALSE),(VLOOKUP(A2 ,'WM2'!A2:C4843,3,FALSE))))

Then for instance at line 59 the formula is
=IFERROR(VLOOKUP(A59,Coors2!A59:D3822,3,FALSE),IFERROR(VLOOKUP(A59,'AB2'!A59:C14001,3,FALSE),(VLOOKU P(A59,'WM2'!A59:C4900,3,FALSE))))

View Replies!   View Related
Vlookup = Lookup Returning Previous Row Cell Data
I have a list of airport codes and I need to look up these 2 digit codes and find the relevant airport and country.

For some reason when I use the lookup function it is finding the correct lookup value but returning the column cell in the previous row.

View Replies!   View Related
Add VLOOKUP To Cell (Macro Code) & Offset From Range For Lookup Value
I'm trying to use an offset from a Named Range as my source cell in a vlookup but get a -1004 Object not defined error. When Debugged the Range.Formula appears to be blank when queried. Using Record a Macro the Reference to a named range worked fine but I could not test for an offset or the use of the range reference. What can I do to use to make this work


View Replies!   View Related
Copy Rows Meeting Multiple Criteria On Multiple Columns
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet

View Replies!   View Related
Count Rows Meeting Multiple Criteria Of Multiple Values
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.

I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.

I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.

View Replies!   View Related
Multiple Lookup On One Row
I am trying to solve a big problem for a project that I have to complete and for hours now I have searching for help as I run over this website. I will place a photo below and explain what the problem is...

I have this row that has 105 cells (I only put some of them for an example) and I have to find from these 105 cells how many 1,2,3,4,5,6,7,8 and 9 exist.

Is this possible to be found through a command?

View Replies!   View Related
Multiple Lookup And Sum
I want to look up dates, list, two different variables (i called it D and P) and add the Ds and Ps which happen within the columns (it may have to skip columns and add). look at the attached image for the problem. i tried various combinations of sumifs to no avail. Is there a VBA code for performing this.

in the problem: list1 looks at the list1 and the date range from the bottom and adds the corresponding D and P values from Jan 2005 to Apr 2005. dates and lists will vary and are inputs.

View Replies!   View Related
Multiple Lookup
I am trying to perform a multiple look up that will look for a value in column B then go over two columns and look for a value then return a value several columns over. Attached is a worksheet with an example.

View Replies!   View Related
Delete Multiple Rows :: Match In Multiple Columns
I need to find all rows that have columns that match in all 3 of the columns.

I then need to delete all but the last row in each "group" of rows.


1 q w e
2 q w r
3 q w r
4 q w r

Delete rows 2+3

View Replies!   View Related
Lookup Many Rows And Columns
My daily receiving list has Item# received that day e.g.
I need to compare this to a Data Base list that has 10000 item# , seller, and status (this data base can have repeated same item# but varied seller and therefore different status
Ing.xls (Data Base file)
Item# Seller Status
123 ABC Approved
123 GMC Not Approved
456 ABC Pending
456 XYZ Not Approved
456 QRS Approved
etc. +10000 items
Now I need to compare the daily receipts to the data base file and pull all the sellers for those particular items and their status which like I said could be on many rows, one row, or not listed at all to the ReceiptList.xls(which I can then make sure that the received items are from an Approved source etc.)

View Replies!   View Related
Rows Value Matches Lookup Value
The table i'm looking up contains 2 rows with a value which matches my lookup value, how do i get the vlookup to pick up both values?

this is what i have:

This is what vlookup picks up:
ACEUSA-001 7,730.84
ACEUSA-001 7,730.84

using this formula:
IF(ISNA(VLOOKUP(I13,'May 2006 MJ'!$A$5:$B$41,2,FALSE)),0,VLOOKUP(I13,'May 2006 MJ'!$A$5:$B$41,2,FALSE))

This is what i need it to pick up
ACEUSA-001 7,730.84
ACEUSA-001 13,948.61

I do need ISNA to take care of #N/A

View Replies!   View Related
Lookup And Copy Rows
Running Excel 2007, I have attached an example of the spreadsheet I'm working on. The actual spreadsheet contains many more sheets and thousands of rows, with several more locations, but the concept is the same.

Challenge 1:
I want to search Computer Name (column C) for names starting with AKL, WEL or WLG and then copy those rows to the NZ worksheet (leaving the original worksheet rows intact).
Then for names starting with DEN, copy rows to the USA sheet.
Then similar for other names...

I have been exhausting myself by experimenting with all sorts of nested INDEX, MATCH and VLOOKUP formulas, but I'm not up to the task and suspect a vb macro is the best or only way to do this..?

I found this post but I haven't been able to adapt it to work in my sheet... probably just my inexperience...

Challenge 2:
The next time the IT dept gives me an updated asset report, I will copy all the relevant data into this 'All Data' sheet, replacing the old data that was there - can the NZ and USA sheets dynamically adjust, or do I have to delete what's there to guarantee I won't accumulate old data? I guess I could use a blank 'template' file each time, if this is easier than building the function into vb...

Once the data is organised in this way, I intend to create a summary sheet that will show a count of assets in each location, probably with a stacked chart to pretty it up.

View Replies!   View Related
Lookup But Offset X Rows
I have a worksheet of data that I am trying to reference to. I will have the data as a "dump" on one worksheet, and lookup from that sheet. I've attached an example of the data dump. I need to essentially VLOOKUP the value (i.e. 054_6425) and then count over 3 columns, and down two rows. I've tried INDEX MATCH, but can't get that to work. Can you get VLOOKUP to count over and down and return a value? File "example" is attached.

View Replies!   View Related
Multiple Lookup Or Sumproduct
My data range is E2:H15. A separte calculation would generate a number in C2. C4 uses Index(match) to finds the closest Vol match greater than or equalin column E. Column E is sorted Desending for the Match. The user then inputs two maximum values for W (column F) and H (col G).

The example problem should return 20 for L. Help! I have been trying every combination of index, match, lookup etc.

Sheet1 *ABCDEFGH1****VolWHL2*Minimum Vol463*800104203Lookup 1Closet Match Greater Than or Equal To480*64084204Lookup 2Input Max W =8*600103205Lookup 3Input Max H =3*600104156****48083207*Solve for L20*48084158****48064209****4501031510****4001041011****360831512****360632013****360641514****320841015****30010310Spreadsheet FormulasCellFormulaC3=INDEX(E2:E15,MATCH(C2,E2:E15,-1),1) Excel tables to the web >> Excel Jeanie HTML 4

View Replies!   View Related
Lookup With Multiple Conditions
way to return a value from a data table by specifying 3 conditions to be met.

Eg. My data table is in cells W1:Z100
(Column names = Track, Distance, Class, Time)

I want to be able to return the time value, based on track, distance and class values.

View Replies!   View Related
Lookup With Multiple Sheets
I have a workbook that has separate tabs for each month. In cell D1 in each sheet is the first day of each month (1/1/08) then in E1 (1/2/08) until the end of the month.
In each sheets - the total for each day is in D30 then E30, etc...depending on which column the last day of the month ended up in.

Then I have a "Report" on using its own worksheet in the same workbook. I would like to be able to type in a date in cell F4 "Report" tab and then for cell A9 "Report" tab to be able to search through JAN D1-?, then FEB D1-?, then MAR D1-?, etc. until it finds the date. Then I would like for it to return the corresponding value found in D30 or E30, etc.

View Replies!   View Related
LOOKUP Across Multiple Workbooks
I am trying to create a spreadsheet that would be used as a inventory location log for our raw materials Everytime a raw material comes in our door, we assign a lot number to it (currently a manual system).

I am struggling with how to show the balance on hand in the 4th column of this spreadsheet.

I want this column to look up the balance on hand for that lot # based on the material code. We have up to 100 "material codes" all with different paths to get to them.

For example:

'T:Material CardsCold Roll[(material code).xls]
'T:Material CardsHot Roll[(material code).xls]
'T:Material CardsWire Roll[(material code).xls]

View Replies!   View Related
Copyright 2005-08, All rights reserved