VLOOKUP With Multiple Entries For The Reference?
Feb 4, 2010
I love using vlookup, but what do I do if the value I'm looking up is listed more than once in my array? The default is that it will use the first value found.
I have a spreadsheet with 1000's of commodities and for each commodity the volume is broken out by month. For example, here's the sheet where I'm pulling the data from:
ITEMNOPERIODESTCSTREQQTYACTQTY
RC0202010010.109949140005867316
RC0202010020.109939312004668300
RC0202010030.109958968000
RC0202010040.109941769000
RC0202010050.109919656000
RC0202010060.109941769000
RC0202010070.109949140000
RC0202010080.109951597000
RC0202010090.109951597000
RC0202010100.109951597000
RC0202010110.109973710000
RC0202010120.109956511000
So my vlookup for the RC020 for the december Actual Quantity will default to the 5867316. For for January (period 2), if I do the vlookup it will still take the 5867316 again since its first in line.
So my question is, how do I alter my formula in January so it will vlookup the 2nd RC020 and give me the "4668300"?
View 9 Replies
ADVERTISEMENT
Feb 10, 2014
I have an excel file containing more than 70,000 lines (items and their corresponding orders) and in second file i have all the items listed. I want to find how using excel functions like vlookup i can return all the orders (from first file) against the items (in second file).
Attached a simple example of my problem. Please note that both tables are in different sheets of an file.
ITEMS
ORDERS
A
2
B
3
[Code] .......
View 7 Replies
View Related
Jan 30, 2009
I've tried to amend the formula's posted above but to no avail so am hoping someone can have a look at the attached.
I would like to be able to use Vlookup or similar to complete a table (starting in cell A19) based on the surname added in B16.
View 2 Replies
View Related
Feb 2, 2009
I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.
A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008 - 2/25/2008 $15
I have another table (table2) in another sheet in the same workbook have a material and date.
A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008
My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.
I have tried using vlookup but it only works for the first match and doesn't check for other values
below is the function that i tried
=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")
View 14 Replies
View Related
Jun 12, 2014
I have a VLOOKUP that needs to bring back the sum of all entries that meet the criteria. At the moment it is only bringing back the first entry.
View 3 Replies
View Related
Feb 16, 2009
So i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events.
I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.
View 14 Replies
View Related
Feb 10, 2010
I need to run a vlookup to find some data. But I have a lot of data about 600,000 lines. Currently this list is spread over several columns (as the limit is something like 50000). How can I make my reference range access multiple columns?
View 5 Replies
View Related
Jun 5, 2007
I input sales for each day of the week on a separate sheet since the beginning of the year. I am taking the average of the last 12 (Tuesdays) but with each input I need to change the function. Say it was Average(B11:B22) with the new entry in B23 I would change Average(B12:B23) I would like it to do it automatically with each entry.
View 9 Replies
View Related
Jan 15, 2010
Writing this workbook in 2007, but it will be used in 2003. Have searched but can't find answer to this. I have a 5-column (A to E) table array and I'm using VLOOKUP in several other cells to return values in col2, 3, 4 & 5 with my lookup value being col 1. However, col 1 will contain numbers which occur more than once. How can I get VLOOKUP (or something else?) to pick the last occurence (lowest row) and use that one? Also, the array will be sorted - smallest to largest - as the rows go down - how will the SORT work on duplicate entries? I'd like, if possible, for the entry made most recently to be the lowest of the duplicate entries so that VLOOKUP (or whatever) will pick that one to use. Will any kind of SORT do that?
View 9 Replies
View Related
Dec 23, 2013
I have a dataset that looks like below -
Header1 Header2 Data
100 101 20.50
102 19
103 1003.2
101 100 20.50
200 300
102 100 20
200 104 30
Now, what i need to do is for each of the rows in my dataset, I need to find the reverse entries (consider first row where header 1 is 100 and header2 is 101, I need to search the entire dataset to find if I have some data for header1 is 101 and header2 is 100). In this case, I do have such data where header1 is 101 and header2 is 100.
Header1 Header2 Data Reverse Entry Difference
100 101 20.50 20.50 0
102 19 20 -1
103 1003.2 Not found Not found
101 100 20.50 20.50 0
200 300 30 270
102 100 20 19 1
200 101 30 300 -270
let me know if that's possible through excel formulas?
I need a column D with such data and column E for differences. So that, my final output should look like below -
View 10 Replies
View Related
Jun 17, 2014
I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.
BEFORE MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118
[Code] ........
AFTER MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911
[Code] ......
What the MACRO would look like? This is for Excel 2010.
View 3 Replies
View Related
Jan 15, 2013
I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows
Example (I have the below in a single Cell as column headers)
NCM Server Mgmt VLAN Site ID
Next Line down is the data (Each row in a single cell)
Enabled 10.10.10.0 50 TEST SITE 1
Enabled 10.10.20.0 50 TEST SITE 2
Disabled 10.10.30.0 50 TEST SITE 3
How I could achieve this as I have a number of projects where this would become useful
I know you can use delimiters but with spaces between the values I just can't fathom a way forward.
View 12 Replies
View Related
May 4, 2014
I have column A and it has 1000 rows, every row has a number in it, from 5000 to 5200, meaning that some numbers are presented multiple times in column A.
I need to lose repetitions, so every number is in the the table only one time and then I need to convert this one long column into, for example, 9 columns, so there's no wasting of space and have only one column in every page, if printed out.
View 5 Replies
View Related
Nov 20, 2009
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
View 4 Replies
View Related
Mar 7, 2009
generally vlookup function gives a value from the right side cell. is it is possible to display a cell from the left side. in the attached excel file i want the numbers against the cell which contains mom.....
View 2 Replies
View Related
Feb 3, 2010
I am working on a data with a range of same customer number but different sales figure. If there any way I can search for a duplicate cusomer number and then summing up the sales? I tried to use vlookup but it only recognise the first reference number. I have attached the excel for your reference.
View 3 Replies
View Related
Mar 31, 2006
i have a vlookup formula that i am using and i have two question
my first is something that i noticed while reading mrexcels book and comparing to what i was told here.
i was refered to use column()+0 instead of what mrexcel says of col(the number)-how many to get the number i want.
now my setup is like this
in a1 i have a sheet named anaheim batting totals in a2-a16 i have week1-week16. now i have my formula that i enter in b2 that uses the reference in a1 to look up in the range in sheet week1( i manually entered the range location.) i then copy paste it down to b17 with the dbl click trick and then manually go into each of those formulas and change week1 to week2 to week 3 etc. i want to be able to reference the A column title(whichever week appears) and the a1 cell in the same vlookup formula. in essence to say lookup a1 in this range ex.a1:l450 in the sheet that is in the corresponding a column,i.e. week1.16 all set the same with the same range. the rest i can figure out, but i would like to have one formula to copy to all cells of my chart.
View 9 Replies
View Related
Oct 5, 2009
I'm trying to use the Combobox1.Value as the look up value in my Vlookup formula. This is creating a type mismatch error at the moment.
I've tried both:
HTML ltb1.Text = Application.VLookup(ComboBox1.Value, Sheets("Wk Data").Range("A3:A17"), 2, False)
and
HTML ltb1.value = Application.VLookup(ComboBox1.Value, Sheets("Wk Data").Range("A3:A17"), 2, False)
Will
View 9 Replies
View Related
Apr 26, 2014
I have two lists of product data, one for buyers and one for sellers (these are listed as A, B, C). The product names are not exactly the same (Eg Playstation and playstation three should be matched), I would have thought using the FuzzyLogic add in to match these would be the way forward! I need to rank the sellers by how many of their items appear on the buyers list
View 8 Replies
View Related
Nov 11, 2008
i am trying to write some code to analyse a weightlifting movement that occurs 3 times. In particular there is a part of the macro where the user will input the start and end time of the movement using input boxes (to only select relevant data).
I want to then use a vlookup function to search for the start and end times in a range (1 column) in the time range and return the cell reference of these so i can select only these values and either create a graph or do more analysis. My code for this particular part so far looks like:
View 3 Replies
View Related
Nov 14, 2008
I have a sheet which uses a vlookup to find the data on a large sheet. normally to get the cell reference of the data i would use cell("address",......
However this appears not to work with a vlookup.
View 10 Replies
View Related
Dec 12, 2008
I have a worksheet that uses a lot of vlookups and I have to update the filenames and locations quite often. I would like to update a single cell rather than updating every single formula.
However I am getting the #VALUE error when trying this.
Simplified Example:
I have: =VLOOKUP(A3,[Table.xls]Sheet1!$A$1:$B$4,2,FALSE)
I would like to place [Table.xls]Sheet1!$A$1:$B$4 into a cell (D1) for example.
And have my vlookup function as =VLOOKUP(A3,$D$1,2,FALSE)
This way I only have to update D1 when I want to change the filename instead of a whole lot of functions.
View 2 Replies
View Related
Nov 15, 2013
I am currently having real problem using the formula below:
=INDEX(Data!C:C,MATCH(B7,Data!A:A))
What i am trying to do with this formula is return the cell address of an item that is in a different sheet.
The reference the for search is in Cell B7 which is a date, it then looks at a different sheet "Data" for this date which is in Column A. It then returns the Cell Reference of Column C of the same row.
Currently it is just returning the value of column C and not the cell reference.
View 3 Replies
View Related
Mar 12, 2008
I'm having difficulty referencing named ranges indirectly in a VLOOKUP formula (Excel 2003).
Cell A1 contains a fuel cost.
Cell A2 contains a formula that returns a RangeName.
In Cell A3, I want to enter a VLOOKUP formula that uses the range returned as the result in Cell A2 as the Table_array. In other words: =VLOOKUP($A$1,$A$2,2,FALSE). The result is #NA!.
If I use =VLOOKUP($A$1,INDIRECT($A$2),2,FALSE), I get #REF!.
Among other things, I've also tried =VLOOKUP($A$1,TEXT($A$2,"@"),2,FALSE). No cigar.
View 9 Replies
View Related
Dec 11, 2008
Never got this message before with the old excel so i dont know how to resolve. I get this. "Invalid Reference. File conversion can not contain formulas that reference cells beyond a worksheet size of 256 columns(columns IW or higher) or 65536 rows." Im doing a VLookup and when i input the table array it is always invalid when i know it is valid. The spreadsheet im referencing does not have any formulas beyond that range either. Another guy i work with is now getting the same error with the new excel.
View 9 Replies
View Related
Jan 9, 2009
I'm using a spreadsheet to assimilate data from a number of different files. Now I need to vlookup on this compiled data, but the vlookup cannot find the value because it is not looking at the value of the data in the cell, but rather the formula. How do I get vlookup to search a column by the value displayed in that cell, rather than the reference to some other file?
View 9 Replies
View Related
Jan 11, 2014
I am trying to create a lookup formula where the cell value to match has a formula behind it.
For example: =INDEX(AR7:AR371,MATCH(G28,AE7:AE371,0))
The trouble I'm having is that the value in G28 is derived from a formula and the Index Match formula then gives a #N/A result. If I change the value in G28 (a date) manually, so directly enter a date, the Index Match formula works ok.
Is there any way of getting the Index Match formula to work, or Vlookup would also do although that at the moment that has the same problem with G28 having a formula behind it.
View 4 Replies
View Related
Jan 22, 2009
I am trying to use vlookup so that when each site name is selected the relevant comments for that site are displayed.
I have a formula that is working, but it displays "0" if there is nothing in the cell. How do i get it to display a blank cell if thre is nothing in the reference cell.
this is the formula that i am using:
=IF($C$4="", "", IF($C$4="No Match", "", (VLOOKUP($C$4,Comments!$A$2:$U$295, 5,0))))
The site name appears in C4, and is selected from another sheet in the workbook.
View 5 Replies
View Related
Oct 22, 2009
I have a workbook where one tab contains the data that I am given:
Project-A data1 data2 date1 date2 date3
Project-B data1 data2 date1 date2 date3
Project-C data1 data2 date1 date2 date3
Project-D data1 data2 date1 date2 date3
In another tab, I have a report where a row containd the project name, and I want to find the maximum value of the dates in the ither tab, where my project name matches the project name on the tab data I am given.
So, if I have "Project-C", I need to know the max of the dates in the row for Project-C; but I dont know what row that will be on in the other tab. For info such as 'data1' I have been simply using vlookup using the project name as a key.
View 7 Replies
View Related
Jan 7, 2010
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 2 Replies
View Related
