I have two column on two seperate worksheets. One worksheet has both columns filled out. On the second worksheet, I want to be able to type in info in once column and have excel pull the other column from the other worksheet.
WKSHT 1 WKSHT 2
ID Name ID Name
1 Bill 3 ?VLookup?
2 Steve
3 Mike
Ive tried using Vlookup('worksheet1'!A2:A4,'worksheet1'!B2:B4,2) but can not get it to work.
I have been creating my own custom Estimate/Invoice system for my Renovations business. I am really stuck on something. I have scoured my Excell Bible, asked numerous message boards and noone is able to help. I did find a reference to my issue here but it still left me confused.
Here is my issue.
I created multiple lists for my materials database. They are broken down by categorie…such as Drywall, Tile, Doors & Windows, Hardware, Etc. right now I have 14 lists which I continually add items to as they come up.
Each list are exactly the same, 3 columns Item, Unit, Cost.
I am try to do a Vlookup to access the cost of an item from these list. Please keep in mind that no item is EVER THE SAME.
I can make a simple vlookup work for example searching my drywall materials database VLOOKUP(A1,Drywall,3,False) with Drywall being the preset list range.
However I want my Vlookup to search EVERY MATERIALS LIST for the specified item. I have tried inserting an array into Vlookup that looks like this (List names are restricted to save space)
VLOOKUP(A1,{Drywall,Tile,Hardware,Paint},3,False)
But I can not get it to work, I can get one single list name to work but not an array of them. Is this possible?
Lastly If statements will not work here as I have upwards of 14 lists and If statements only allows nesting up 7 times for a limit.
I have drop down fields using a vlookup to grab the corresponding data in a colums next it which is being concatenated into a one big cell. The problem is a set of other columns that I need to pull data from but its dependant to a previous column. In the attachment you see which ever region is selected the following column data is grabbed and the same goe for title. Now when the location is selected the info in its column should be selected that corresponds to the row that the specific title is on. example
if selected: North America>Secretary>Texas results: A Crazy Mix->;typical,Notepad, pen,square dance
I would like to select a item from a list and have a text box display data from the next column (corresponding row) Also, I would love if I could type something into a second textbox and have that copy onto a third column (again, corresponding row) Also, if the add comment command button could transfer that record to the "commentted items' sheet. I have attached an example.
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code: Public Sub CSVparser(file As String) Dim Lines As Variant Dim j As Integer Lines = Split(file, vbLf) ReDim breadCrumbs(UBound(Lines)) As Variant For i = 1 to UBound(Lines) - 1 breadCrumbs(i) = Split(Lines(i), ",") Next i End Sub
I have attached a sample from aspeadsheet I am working on. The option reply sheet has '1' entered if the student has opted to take this course. On the other subject sheets I would like the names who have opted for that subject to appear. Stuidents can opted for multiple subjects. I have tried Match and Index functions but it is difficult as students can opt for more than one.
what I'm looking at doing is counting the number of reccuring unauthorised absences in a list. We have the persons name in column B, the absence type in column C and the date in column E. Basically I need to send out an AWOL notice when 5 days of unauthorised absence for the person in column B has occurred, so I need some sort of indication that this has happened in order for me to stick some conditional formatting in there to flag it.
I'm trying to create a template that will be able to return a sales persons call target based on the category they sell and the current level they are. It works when I only ask it to look for one or the other, but I'm getting stuck trying to make it use both. I've attached a small sample. My original equation is as follows
and this works perfectly fine. The problem I've now got is I need to add in a second criteria for setting the row_num. I've tried the following but I get a ref error. =INDEX($C$2:$Q$51,AND(MATCH($B3,$A$2:$A$51,0),MATCH(D3,$B$2:$B$51,0),MATCH(G$1,Telesales!$C$1:$Q$1,0)))
I have a procedure that I want to pass two arrays to. When I pass a single array it works fine but as soon as I try to pass two arrays I get a syntax error. Both arrays are declared exactly the same way and are used in the same way. Am I limited to passing only one array per procedure?
Now, I need a formula that will sum the entire column B, by looking up the array of column A on another sheet and only summing the numbers on sheet 1 that also have a value of "West" on sheet 2. Sheet 2 looks like this:
This needs to be functional as a template to be used each month - the entire point of the formula is to save time and eliminate the current (and lengthy method that is in use). I can tweak the formulas as needed. But am trying to avoid having to concatenate anything or do any modifications to the format of the sheets.
I'm looking to look up sheet name based on a date, then look the date up within that sheet which is a merged cell, then return the figure in the corresponding cell.
I have a sample worksheet to attach, just have to work out how to do it.!
In the sheet "Cashflow Summary" in cell E24, i want to lookup the sheet name based on the value in C22 (merged cell), then lookup the date in that sheet (6 August 2014), and return the value in cell y8.
The difficulty is with firstly the merged cells, but also performing the lookup over a number of different arrays, as the sheets for each month, have the months listed under each other in rows of 5 days at a time.
I like this formula a lot and have used it more often, however it has made my file exceedingly slow..
Considering that it takes every small change in the file to compute around 5 mins or more than this the whole purpose seems to be defeated, I have read a lot of posts on Array Vs Non-Array of late by DonkeyOte.
Is there a formula to look in multiple arrays and return whatever it finds i.e. text or numeric values?
Sheet1  ABCDEFGH1Cat0 CatFive Donkey32   Mouse2 Wolf43 4WolfFour CatFive Donkey35   Mouse2 WolfFourSpreadsheet FormulasCellFormulaB1=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,VLOOKUP(A1,$D$1:$E$2,2,0), VLOOKUP(A1,$G$1:$H$2,2,0)))B4=LOOKUP(REPT("z",255),CHOOSE({1,2,3},0, VLOOKUP(A4,$D$4:$E$5,2,0),VLOOKUP(A4,$G$4:$H$5,2,0)))
I am trying to multiply one array of prices for multiple input products across the volume that that product my go into multiple end products.
I know you can use SUMPRODUCT with two arrays that are equal BUT I want to Multiply the price matrix across 15 or 16 other matrices and sum all the products. The price matrix and the other arrays are all 1 x "whatever".
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.
This is as far as I have got but even this won't work:
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:..............
I have a spreadsheet with 5 worksheets. I want VLookup to read the City and State in the two columns next to the County Column in the custmast worksheet. Compare the city and state to the worksheets AL, FL and GA worksheets and return the correct County and Territory Code found in the Territory worksheet in the County Column and Territory Column in custmast worksheet. I've attached a file for better understanding.
Is there such a thing as a multiple value Vlookup? Basically, here's my problem (please refer to image):
On the left are columns for Date, Time and Price. On the right, there is a blank column for Req Price which has to be filled with the corresponding price based on the Req Date and Req Time. So for cell H2, there should be the price that corresponds to 1/4/00 and 8:25:00, which would be 97.00 (which comes from cell C4). If say there is no price that corresponds then the cell is simply filled with an #N/A (which would be the case for cell H4). Then so on and so forth for the remaining cells in column H.
Is there like a Vlookup function wherein you can specify 2 lookup values or something? Or should a macro be used for this sort of thing?
I am trying to simplify a complex array formula using a UDF. I am trying to return multiple corresponding values horizontally. Can this be translated to VBA?
I've used VLOOKUP based on matching one column and returning one result. No problem. Now I need to return one result based on 3 columns matching.
Assume this range of columns is named: TravelDays Origin...Location...ShipVia...TravelDays 33778...420...UPS...3 33778...425...UPS...1 33778...440...UPS...3 33778...443...UPS...3 33778...446...UPS...2 33778...447...UPS...3 33778...449...UPS...5 33778...475...UPS...3
What I'm trying to accomplish is if these columns match: i2=Lookup!TravelDays,1 E2=Lookup!TravelDays,2 N2=Lookup!TravelDays,3
I am trying to create a table which will search for a name throughout several other sheets. All of the same format and layout but with different data. I then need to display information in a different column of the same row of the name I'm searching, just like vlookup.. I also need to be able to search for multiple entries with the same name, so there may be 3 different entries for John Smith thoughout the few dozen sheets.
I have a requirement where I need to look up a resource hierarchy with the region and the product line and then decide the value from the mapping table.
May be I can explain more clear with the workbook example attached.
If a user belongs to Dev & QA * hierarchy, check the region, and correspondingly check the product line, on meeting all these select the value from the mapping table to fetch the expense value from Approval process column of mapping sheet.
I'm trying to create a VLOOKUP that will return a value in 'Tracking' from 1 of 4 different sheets. If the value isn't found in sheet 1, the lookup should continue to sheet 2. If not found in 2, move to 3 and so on. In cases where there is no value, I would like to return " ".
This formula returns values only in sheet 1. It won't continue to 2 and so on. Also, this will return a 0 for a blank value. If working as intended, 'Tracking' will show "g" in B2 and " " in C2.
I've been trying to figure out how to do a vlookup using two conditions. Reading some other posts, I saw this could be done by combining a MATCH function but I'm still having trouble. Here is what I'm trying to do: I am trying to match cash amounts based on two look up values....one being the identifier (cusip) and one being the pmt code. http://i105.photobucket.com/albums/m204/RAK_08/data.jpg
This data is formatted exactly the same but comes from two different sources. I need to match the pmt amounts for a cash reconciliation.
