I have a double vlookup problem here.
Here's a simple illustration:
A B C D E F G
1 1 1 1 One
2 2 2 2 Two
4 4 3 3 Three
3 3 4 4 Four
5 5 5 5 Five
I need to match A,B with E,F.
If they match, I need to display the string in G in C.
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies View RelatedOn one sheet I have an array of 4 cols. It looks like this:
123 Chris 12345 Jones
123 Chris 23456 Pete
123 Chris 34567 John
On the main sheet there are also 4 cols. In cell A1 I enter a number, in A2 I have a VLookup to get the corresponding name for the number entered in A1, which works fine, I type 123 in A1 and get Chris in A2.
In a perfect world I would like A3 to become a dropdown with 12345, 23456 and 34567 so the user could then select one of those, and by doing so, A4's VLookup would give Jones, Pete or John.
I don't think a dropdown can be populated this way - am I stuck using VBA?
I have a calendar (tab March in the attached file) where I can indicate the delivery package of tab List. The problem that if the delivery happened twice a day, only the first one will be indicated. If there is any way to indicate both deliveries?
View 2 Replies View RelatedTrying to do a double lookup, or a conditional lookup. Attached spreadsheet will provide much more clarity. I have multiple data sets within a universal file, each data set can contain the same constituant therefore I would like to lookup/vlookup a value only if the constituant is part of the data set I want to pull from.
In attached sheet: if cells in Col A= "F", then vookup("RDT",(array would be data set "F" (I think)),return value in col 2 (assuming vlookup is in col B), t/f)
I have a table of data with two rows of unqiue identifiying information. I am trying to develop a formula that will allow me to do a vlookup twice, once for each row of unique identifying information to retrive the value in same row for the third column. For example:
apple red 50
aaple blue 75
aaple yellow 80
apple green 90
grape red 12
grape blue 17
grape yellow 45
grape green 41
So, I am trying to do a lookup where I can say what is the value in the third column for apple and red. Or, what is the value in the third column for grape and green.
I have a spreadsheet of mail types with totals. What I am trying to do is create a formula in the highlighted cells in the second sheet that will look at the Account ID Row for a certain value (in this case '45265637'), then look for all the month (in this case April) then add all the figures for this month in the LETTERS row, with the cell below it doing the one for PACKAGES.
I am exhausted from trying to get this one done. The reason that I need to this is that is I have an Excel system readout of 821 different ACCOUNT IDs and their volumes for everyday from 1st April 08 - 30th June 08 and I need a forumula that will extract this automatically as I don't fancy manually cell referencing 45,000-50,000 cells!
(and I hope the images work!)
http://www.mediafire.com/imgbnc.php/...5942a7002g.jpg
http://www.mediafire.com/imgbnc.php/...a3b756c52g.jpg
I am tring to find a more efficient way of avoiding a double Vlookup function.
=IF(ISNA(VLOOKUP(A6,Sunday!$A$72:$AM$86,38,FALSE)),"",VLOOKUP(A6,Sunday!$A$72:$AM$86,38,FALSE))
I have read over this forum but still haven't understood any solutions.
A6 is a name.
The name may not be on the worksheet the function is looking up.
I have a table that shows a row of dates, a row of campaigns, and then a row of values I need to reference. I need to pull the values for a specific date and corresponding campaign.
For example, I need to pull the value for campaign 'notset' on 6/16/09.
I tried the following, but it would only work on the first and last campaign: ...
I'm curious if there's a way to use the Vlookup function to compare TWO values in TWO cells with two values in two OTHER cells (same row though) and return one value from another column. Something like
Vlookup(A2 & C2, Sheet2!A1:C400, 3, FALSE & FALSE)....? Does that make sense?
Say the values in A2 and C2 are in A24 and B24 respectively in Sheet2. Can it return C24 for me?
I have two spreadsheets, one with master file with original data and one that needs to pull in the original data. My issue is all the data to match off of is in the same column, and a number of other files link to the master file, plus it is used externally, so I cannot alter it, and I would rather not create a mock/copy file. Is there a formula that can look for 2 different items within the same column? Would prefer not to use VBA, but if that's the only option I'll take it. I am using Excel 2003.
Here is an example of the setup - I would need the formula to reference off the two different items/categories in the column, so lookup off the 'St. Louis' and following that, lookup off the produce items.
Let me show you how my sheet is set up.
Steeves 10 ny
Steeves 15 ny
Steeves 20 nv
Steeves 5 tn
Steeves 10 ca
Keppler 16 tx
Keppler 20 nj
Now for this example I need to sum column B if A=Steeves and C=NY.
In this example it would return 25.
Is it possible to enter double inequations? Excel returns "false" even it is obviously true. If I type =1<2<3 the result is "false".
I can get the correct result using =and(1<2,2<3), but that makes it longer, and if I have more formulas inside instead of numbers I'll have to repeat the middle one, it will take the place, but only in oreder to compare it again.
Is there anything that can be done with this?
I have a list with fractions such as 25 1/4, 33 5/16, 20 15/16, etc. i have them formatted as FRACTIONS 2 DIGITS 21/25. Manny of those have double spacing ex: 25 1/2, So i am wondering if theres a way to take the extra spacing off the ones that have double.
View 10 Replies View RelatedI am attempting to create some code that has 2 loops in it.
THE SITUATION:
I will have 2 ranges (eg B54:B100, and B106:B149 - the problem is I will arrive at these ranges by code such as: Range(Selection, Selection.End(xlDown).End(xlDown)).Select)).
WHAT I THEN REQUIRE
I need to have some code that will look through each cell in the second range and if the cell contents is found in the first range then the entire row from the second range will be deleted.
=INDEX('sqft - PC each bldg'!D2:D1705,MATCH(Sheet5!B3&Sheet5!F2,'sqft - PC each bldg'!A2:A1705&'sqft - PC each bldg'!C2:C1705,0))
my result is N/A
I wonder if it has to do with both lookup ranges being columns?
Have a quick one, trying to figure out a simple expenses/income/saving tracking table for my wife and in-laws; have figured out most of it. Will use pivot to extract most of the data, but on a summary sheet I need double condition sums (or at least I guess so...). For example I have "amount" column (which is expanditure) and date column (have used function to extract month and year for other purposes to separate columns), how can I calculate multiple costs (all the costs/expenditures in a month) that have taken place in eg. april 2008 only/ since I want to display it on a separate sheet as a summary overview...here's the link to imageshack, dunno if it's gonna work:
[url]
Have tried something like: =SUMPRODUCT(month="4")+SUM(amount) but it sums up the entire column...
or this one: =SUMPRODUCT(ISNUMBER(MATCH(4,month,0)+SUM(amount)))
I know I miss a bunch on the last one
I have a list like this:
xxxxxxxxxx1-jan | 2-Jan | 3-Jan
-------------------------------------------------
Location 1
Bob Smith ----B2--- C2 ---- D2
Frank Thomas-B3---- C3---- D3
Location 2
Bill Clinton----ETC ALL
George Clinton
And another sheet, a similar list, (just names and randomly sorted) but populated with the values I want,
xxxxxxxxxxx1-jan | 2-Jan | 3-Jan
-------------------------------------------------
Bob Smith --- 2 -----5------ 1
Frank Thomas 3----- 1 ------2
Bill Clinton ----4----- 5 ------2
George Clinton 4----- 4 ----- 4
I want to check the name in A2 on the first sheet against all of column A on the second sheet, then find the number value for the correct day for that name.
How can I write a formula that will look up these values for me? I looked at OFFSET but unfortunately I don't have a lot of excel experience (yet)
I am trying to avoid double counting two separate rows with the same columns. For example:
StockFund AFund BFund CFund DFund EFund FFund GCountDesired Count
LKOH111100044
LKOH001111042
If I have two stock codes the same in column A (not necessarily one below the other), I only want to count one of the occurrences per fund. So I would like to avoid counting the duplicate records for funds C and D. The desired outcome for the total holdings of LKOH should be 6 and not 8.
I am trying to lookup the value that corresponds to three sets of data. The formula I have been using is but I am still getting #NA. I’ve attached the spreadsheet
=INDEX($B$3:$I$17,MATCH($B21,$A$3:$A$17,0),MATCH($A$22,$B$1:$I$1,0)+MATCH($B$20,$B$2:$E$2,0)-1)
I am looking to search Vendor name 1, Actual and A to return the value of 1.
I am trying to write some questions on cells of Sheet1 of a workbook. Then I want to write the answers on cells of Sheet2 of the same workbook. I want the user to be able to double click the question-cell on Sheet1 and be taken to the respective answer on Sheet2.
View 6 Replies View RelatedI have a spreadsheet that just uses the basic "SUM" function. This morning I went to use it and the function does not work after I change a number within the field of that function. If, however, I go into the SUM function and double click, then hit enter, it does the new calculation.
View 3 Replies View RelatedI'm trying to simplify some data entry on an excel sheet, by making a double click vba to add a time any where in 2 columns, specifically H and I .
[Code] ....
I tried to modify this code but my understanding of VB is very lacking.
I have a excel sheet with a macro set up. On the main sheet is all the information with expiry dates. Now each entry according to the expiry date goes into another sheet (Monthly Sheets) in the same document. However if you add to the main list and press go it re-sorts the list and duplicates are put into the monthly sheet. (Hope that makes Sense)
Here is the code from Naras for the sorting:
I have a timesheet which for certain days of the year calculates double-time. What i would like to do is have a formula that gives double time for a 24 hour period. I have a formula setup with a start time and end time for my double time, but it wont stop at midnight.
View 11 Replies View RelatedI have this list of all rangenames in my workbookin column A:A
For some reason I want to doubleclick on the rangename in the list to activate this range.
The reason is that I want more smooth activation-prosess; The Excel Range List is too long to practical use as a direct lookup, so I want to activate directly from my own list (where i have added definitions to the range names.
I have tried to build a hyperlink formula, but failed. Maybe the macro below can be modified to do the job, but I am stuck here as well.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim RangeName
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Cancel = True
[Code] ...........
I am trying to create a VBA code that will search through a column"A" and if it finds a calue that C1 then color it, when it finishes searching columnA then loop again and search for items that match C2
Here's what I have so far with no success
Sub ColorItems()
Dim i As Integer
Dim j As Integer
Range("C1").Select
For i = 1 To Selection.CurrentRegion.Rows.Count
For j = 1 To ActiveCell.Offset(0, -2).CurrentRegion.Rows.Count
If ActiveCell.Offset(0, -1) = ActiveCell.Value Then
With ActiveCell.Offset(0, -2).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
I have a spreadsheet with two drop downs, one lets say has country and the other city. When I select a country in the country drop down, I want to be able to only select cities in that country.
I used a method that I found in an earlier post, where I define the name of list of cities of a country to that country and then use the indirect function in city dropdown down validation. The prolem is I can still get mismatched city and country because when I change country, I still have the city from the previous selection untill I drop down and select the city again.
I would like the city to show blank when I change the country until I select a city from that country.
I have in "column A" an accountnumber and in "column D" a lastname.
The lastname can appear more than once and the accountnumber also.
Though I want combobox1 on userform1 to contain the lastnames.
It's possible to contain a double lastname as long as the accountnumber isn't the same.
For instance:
accountnumber: Lastname:
1234 Johnson
7890 Johnson
here Johnson will be insert twice into the combobox.
accountnumber: Lastname:
1234 Johnson
1234 Johnson
I seem to have broken something!. Whenever I double-click to open an Excel file (.xls files are associated correctly), Excel opens, but nothing happens. That status bar says "Ready," but my workbook never appears.
If I do a File-> Open within Excel, then I am able to open the workbook. I have no idea what is going on. This occurs for all Excel files whether they contain macros or not.