Lookup Item That May Be In One Of Multiple Columns, Display From One Column
Jan 1, 2010
I would like to put a formula (or make a macro) for Cell F7 (and all the other cells too) in sheet "Budget Sheet" that pulls the value $100 from cell G5 from sheet "Input Sheet" by looking through columns A-F for the account ID "5111100"
Obstacles:
- I (for the most part) cannot change the format of "Budget Sheet" or "Input Sheet"
- I cannot have the formula (or macro) just look down Column F because the actual file I am working with has the pertinent account IDs scattered through columns D-F (it was exported from another program).
Notes:
- I am using Excel 2003 SP3
- If necessary, I can make the reference to "5111100 · Bonus" if that would make things simpler
- Column G will always contain the dollar amount I need.
-I've been hitting the books trying to figure out how to get array formula working on this and cannot for the life of me get it to obey when I press ctrl-shift-enter.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I am trying to do a two way lookup with multlple results. In the example attached I want to know the names of the people who were in Boston on 01/02/09. I have tried a number of index, match formulas to no avail.....
I am hoping to create a drop down list of months in one sheet, and when I select a certain month, columns in about 10 other worksheets in the same workbook will either hide or unhide columns...
The spreadsheet is laid out with columns (C-N) for each month in the year, for actuals, then columns for budget and budget variance (O-P), then YTD Actual, YTD Budget and YTD Variance. When I select September, for example, I want October-December to hide, and leave Jan-Sep unhidden, while keeping the budget, YTD and variance columns.
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 work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.
I am trying to display multiple columns in my combobox on my userform.
I have it where it will find and load the data but when i select from the data only 1 column shows is there a way to have all 4 columns show up?
Dim lrowzz As Integer lrowzz = (Sheets("graphs").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row) - 1 'new row to enter data into NEXT AVAIABLE ROW Me.capac.List = Sheets("graphs").Range("B43:e" & lrowzz).Value 'populates the capa combo box on change capa tab
that is my code to populate the combobox using a dynamic range from B43 - D & Lastrow with data
Column b Column C Column D Column E 333 infoa dateA LocationA 334 infob dateb locationb 335 infoc datec locationc
when it loads the combo box i can see:
333 infoa datea locationa
But when I select 1 of the lines all that is displayed is Column B Data. How do i get it to still show all of it?
I have been already fighting for about a week with this problem. I attached the Excel-file. I have three input data and as output I want to get also three different data. For Vendor I use the VLOOKUP function. For agreement# I have used (not in this file) CONCATENATE by adding an extra column to the data table and then use VLOOKUP.
The actual problem is to find the price because it is based on the three input data. I can't seem to find a function for it. I've tried INDEX, MATCH, IF, nested them for as far as my knowledge reaches. The problem lies for as far as I know in the del.date cell. For example, input date is: Client#:912345, Product:E569831, del.date: 16.7. (July 16th). This input date should return cell G12.
I am trying to create a formula that will look in 3 columns containing 300 rows, and give an answer if the criteria in all 3 columns is met on a single row. example: A B C 1 Blue Bag 60 2 Red Bag 100 3 Blue Bag 60
I want to check ALL the rows in the 3 columns that satisfy the criteria "Blue" and "Bag" and "60" and count how many times this occurs. I have tried COUNTIF but with no success, unless I am doing it wrong.
and I want to look up an approximate value in Col2, say 310, and return it's next-highest friend from Col3, in this case "g". This is easy enough with INDEX and MATCH. But I want to take it one step further and only use those values which are approved by Col1 - so in this case I want 310 to ACTUALLY return "e"
I found some good information here which gets me close. The following works very well. It uses a boolean & operator to match two values at once, but it only works for exact matches. This example goes down the list and finds the first "sydney" which has an "x" and gives the result "h".
When I use this approach on data like in my first example it falls over, my guess is because the boolean & falls down before the MATCH function has a chance to accept an approximate match.
I've been working this for ever and can't seem to figure out the best way to go about it. I have attached an example sheet. All I need to do is figure out the Dept #... which is listed in Row 1, Column F:H. I want to match the project numbers and then return the AA, BB, or CC in Column B.
I'm using an index array formula I learned here to lookup a value between 2 sheets in a workbook. I have had no trouble using it when matching just 2 pieces of information (first & last name), but now I need to make 3+ matches to get the value & the formula is no longer working.
When I check "show calculation steps" in the attached example, I can actually see that all 4 items have successfully matched across the 2 sheets but for some reason the formula is still not pulling in the desired value. Am I wrong to think that I can match more than 2 items w/ this kind of a formula?
Can you display items from 1 column on a worksheet in 2 columns on a Listbox?
This is to avoid having a Listbox that is too long for the form, (I know I can use a scrollbar and I am but I would still like it shorter if possible).
I'm working on a database right now for our school, similar to a directory. What I want to do is be able to look up what items a staff member has been issued and when/if they were returned. What I have right now is one sheet with all the information about the staff that looks like the one below. I want to create a separate sheet where you could look up a staff member by last name and can find all the information in the array relative the the specific cell looked up.
Last Name First Name Position Building Items
So, on the "lookup" sheet, I want to be able to input either Doe and have the look up return cells B2:E6, or if I input Smith it should return cells B7:D11.
I've been experimenting with Vlookup, hlookup, index, and match but haven't been able to get exactly what I want..
I am looking to write a search function that searches through a whole document. The only examples I can find are of a could different search functions but they all are searching an specified individual column and even then I didn't really understand them. My worksheet has 6 different columns which are all different labels for a certain tool. I want the user to be able to input any of those 6 labels and have all the information in that row be returned.
I have a worksheet and for data validation, i do have a dropdown. This is working fine.
I am now trying to create a userform. And I feel for data integrity, I should be using a listbox on the form as well.
I have found examples of different ways to load the list, so I don't think this part will be an issue. But what I cannot find, is if the worksheet row currently has a value (say it is the third item on the list), how do I show THAT on my form?
Example, say we have a list of 10 cities. Orlando Tampa San Francisco London Athens Paris Chicago Cairo Seattle Atlanta
My wordsheet has the rows populated with the cutomers favorite city to visit. For John Doe (row3) in column 5 is the city "San Francisco"
When I use my nav keys, how would I "stuff" San Francicso in to the display and still have it able to select a different city?
Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"
Things to note:
a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.
b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".
If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".
If that value does not occur in sheet B, the corresponding cells should remain blank.
If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.
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..
Im using code from website: http://www.contextures.com/xlDataVal11.html
to display a combobox when I double click any cell that contains a validation list in it. I'm not great with VBA so I am having problems finding what each part of the code does. This is causing problems for another bit of code that I use to display a msgbox when certain values are selected from the list.
The MsgBox shows up great when I select an item from the validation list but does not work at all when I select the same item from the double-clicked combo box. It would be great if I could get the MsgBox to work both ways. This is the code that displays the MsgBox when target value is selected from list
Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.AddressLocal(False, False) Case Is = "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12" If Target.Value = "H" Then MsgBox "This is a pop for extra information. Savvy?", vbYesNo, "Yo!" End If End Select End Sub...............
I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.
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.
macro for an Excel 2007 file. It has to be a macro. I have cells in two columns I need to look at. Column A will have a value of cat, dog, or mouse. Column B will have a date. If A2="mouse" AND B2<"1/1/2013" Then A2="" ElseIf A2=mouse" AND B2>="1/1/2013" Then A2="mouse". I don't need anything to happen if A2 is equal to cat or dog. The macro has to move down the entire A and B column: A3/B3, A4/B4, and so on until the end of the columns. Hope this makes sense. I've tried a few macro samples I found online but they don't quite fit what I need done, or I don't know how to modify it.
I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).
Column A | Column B | Column C Los Angeles | Fire Dept | 3 Los Angeles | Health Services | 12 New York | Fire Dept | 8 New York | Health Services | 22 New York | Internal Services | 100 New York | Public Works | 7 Chicago | Health Services | 15 Chicago | Public Works | 56 Chicago | Social Services | 4
And I am trying to make it look like this:
Fire Dept Health Services Internal Services Public Works Social Services
