Double Lookup For Name/Value
Jan 13, 2010
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)
View 9 Replies
ADVERTISEMENT
Jan 16, 2009
=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?
View 9 Replies
View Related
Feb 17, 2009
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.
View 2 Replies
View Related
Apr 2, 2009
I am trying to get a cell in my spreadsheet to look up a value based on two values. I have a dropdown list that lists the worksheets in the workbook, and each worksheet has a table with width measurements for the columns and height measurements for the rows. I have a function that is mostly working, it calls the data from the proper worksheet, but it rounds the measurement values down, and I need it to round to the next highest value on the table.
For instance, the measurement may be 55" x 55" in, but the table has values for 54" and 60". The current formula rounds down to the 54" measurement, but I need it to round up to the 60". I have attached what I have so far with further notes and cells highlighted.
View 4 Replies
View Related
Feb 19, 2010
I am having trouble getting the double lookup formula to work with a table. See attached sample.
View 2 Replies
View Related
Apr 14, 2009
I need is spread across various rows. In a separate sheet I want to be able to lookup two identifiers and pull the number needed in the adjacent row.
The first identifier is a 3 letter character, the second is CURREVO and I need the number to the right of CURREVO. My problem is CURREVO is not always in the same column, but in relation to the 3 letter character is always in the same row.
A B C D E F GDMGCURREVO52011.25YTDREVO243085.00DTYCURREVO11892.50YTDREVO59783.50ECUCURREVI1943.00YTDREVI 5,541.25CURFRQI4.00EEGCURREVO32864.75YTDREVO205426.75EICCURREVO658761.26YTDREVO3507022.68EILCURREVO335741.70YTDREVO1720830.72ENTCURREVI161242.39YTDREVI638681.84CVLCURREVO796266.21YTDREVO4816890.98ENDCURREVI34479.19YTDREVI44074.54EYECURREVI11880.12YTDREVI 108,007.02(Null)MICCURREVO1098694.15YTDREVO5766072.54NEUCURREVI25251.90YTDREVI 158,236.60CURREVO207.00
i.e.
in a seperate sheet, I want to pull NEU, in that row I want to find CURREVO and bring back the adjacent number = 207.00
View 9 Replies
View Related
Mar 15, 2013
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.
View 4 Replies
View Related
Mar 27, 2009
I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.
Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?
Or in the attached file, I would like to say A and 15 and get A15.
View 3 Replies
View Related
Apr 7, 2009
I have a spreadsheet in which I am trying to track invoices billed according to month in question. For example, I need to find total invoices billed in February for ABC Consulting Company. I have a database in the same spreadsheet that contains all invoices billed for an entire year for all companies. How do I pull invoices for a particular month only, in this case for the month of February? I have attached an example of spreadsheet in question. Included is a tab which indicates desired results.
View 5 Replies
View Related
Apr 18, 2013
I get a report each day with a list of issues. the "group" that works the issue and the "priority". Based on these two factors, i need to do a double lookup (vlookup?) to another tab or file to match the priority and group and see what value should be brought back for each lines results. For example, if group1 had a prority3 issue, the lookup would find the value from the other sheet or file and bring back the value and put it at the end of the row where the formula is.
Attached are examples of the sheets.
sheet1.jpg
sheet2.PNG
View 4 Replies
View Related
Mar 25, 2009
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.
View 9 Replies
View Related
Nov 24, 2008
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?
View 6 Replies
View Related
Aug 19, 2009
On 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?
View 9 Replies
View Related
Nov 16, 2006
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 Related
Oct 21, 2007
I 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.
View 9 Replies
View Related
Aug 26, 2008
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.
View 9 Replies
View Related
Jun 19, 2009
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
View 9 Replies
View Related
Apr 7, 2014
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.
View 3 Replies
View Related
Mar 26, 2008
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: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies
View Related
Apr 27, 2009
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
View 3 Replies
View Related
Jan 2, 2009
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
View 7 Replies
View Related
Jun 12, 2009
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies
View Related
Mar 28, 2014
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 Related
Apr 7, 2014
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.
View 2 Replies
View Related
Jun 11, 2009
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 Related
Sep 30, 2009
I 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 Related
Sep 30, 2009
Trying 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)
View 6 Replies
View Related
Jun 1, 2014
I'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.
View 10 Replies
View Related
Jun 10, 2008
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:
View 13 Replies
View Related
Apr 21, 2009
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 Related
