Lookup Data Between Set Values
Apr 16, 2008
In D16:D29 I use vlookup to return values from named range 'mforcetable' based on value in cell C16.
However, if values are entered in cells I36 & K36, I37 & K37 and I38 & K38 I need the lookup to return the values in 'mforcetable' including and between the 2 values.
See attached example.
Based on value 2000 in C16, what I need to see in D16:D29 is
10.0
20.0
40.0
100.0
100.0
200.0
400.0
600.0
800.0
1,200.0
1,600.0
1,600.0
2,000.0
0.0
Where the 100.0 and 1,600.0 are repeated because both values are specified in I36 & K36, I37 & K37 or I38 & K38.
View 4 Replies
ADVERTISEMENT
Mar 1, 2014
I have two values I need to find in a chart.
(See Attached)
I can do a Vertical Lookup but don't know how to search for the second value to identify how much to offset to retrieve the value.
View 2 Replies
View Related
Nov 8, 2009
I'm trying to return the next unique item in the list of partnumbers. The count column indicates that there are that many instances of that part number. I then populate down than many to VLOOKUP the value "count" number of times. The formula I am using to get the MFR code compares the pn to the one above it. If it matches, I want it to return the MFR offset by the one above it for that part number.
Source data: (will alwyas be sorted by part number, then by MFR)
Part NumberStock num NAME MFR
BDEGHS55555544333RIVET,BLIND53551
BDEGHS55555544333RIVET,BLIND92215
BDEGHS55555544333RIVET,BLIND98996
BDEGHS55555544333RIVET,BLINDC4293
Results:
CountPart NumberMFRStock numNAME
4BDEGHS5555355155544333RIVET,BLIND
4BDEGHS5555355155544333RIVET,BLIND
4BDEGHS5555355155544333RIVET,BLIND
4BDEGHS5555355155544333RIVET,BLIND
I want the first vlookup part of the formula to return the NEXT MFR code in the source data.
View 5 Replies
View Related
Dec 2, 2005
I would like to insert a function in spreadsheet A that looks up data in
spreadsheet B based on _two_ comparison values.
The two comparison values in spreadsheet A would always be in the same row
and the corresponding match in spreadsheet B would also have to be in the
same row.
For instance, if the comparison values in cells A2 and B2 of spreasheet A
match with the comparison values in cells A30:B30 of spreadsheet B, then
show the value that is in column C30 of spreasheet B.
View 9 Replies
View Related
Aug 1, 2007
I have all my data on sheet 2 which contains tables that relate to information on regions, manufacturers, etc so it can be a large amount of tables which will change from month to month depending on a monthly list.
For example, if “Region 1” is in cell B134 – the resulting data I need to pull out will be contained in C138:G232 ...or ”Region 2” which is in B235 – table info is in C239:G333, etc, etc.
On sheet 1, I have a cell (L7) that is populated by another formula. This cell is effectively my lookup to pull out information from sheet2 to populate cells in (sheet1) AE119:AH212 - same sheet as the reference (L7).
I need to look up the reference/resulting value that is in L7 on sheet1, find and match against the values in column B on sheet 2 and then pull in the corresponding table information.
If the value in L7 was not changing at all I could do.. in cell AE119… =OFFSET(sheet2!B134,4,1,1,1) etc etc
... but I am at a loss as to the value in L7 changing and incorporating a lookup… or maybe I am looking at it wrong..?
I have looked up match, offset, index and lookups on the forum and have managed to confuse myself even more. I have even tried taking some of the example formulas and amending with my references but to no avail.
View 10 Replies
View Related
Oct 24, 2007
I have a sheet that has to look up value on a report from a sheet sheet that has more than value. Ex)
On the report I have values 123A and 1234A
On the data tab I have table that has.
Ref Tot Value Desc Value1 Value2 Value3
1 123A Widgets 1 2 3
2 123A Widgets 4 5 6
Ref Tot Value Desc Value1 Value2 Value3
1 123B Nuts 7 8 9
5 123B Nuts 1 3 5
Ref Tot Value Desc Value1 Value2 Value3
7 1234A Bolts 2 4 6
11 1234A Bolts 3 5 8
So the report need find the values for Value 1, 2, 3 on where the value matches the data tab.
View 14 Replies
View Related
Jul 17, 2009
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
For example:
A B C D
Alabama ABC Company Active Expires December 2009
Alabama 123 Company Expired Expired April 2008
Alabama XYZ Company Active Expires August 2009
Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.
View 9 Replies
View Related
May 18, 2007
im trying to get the lookup answer based on two values the current formula i have is
=VLOOKUP(AND(A1,B1),Sheet2!$A$1:$C$31,3,FALSE)
which naturally returns N/A... i might even be using the wrong formula?
View 2 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 25, 2014
Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.
I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).
I've investigated the techniques in this link: [URL]
Questions:
1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?
2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?
View 5 Replies
View Related
Nov 12, 2008
I have 3 Sheets named Paid, Rejected, and Reprocessed.
On the Paid and Rejected sheets I have 2 fields Customer # (Column A), and Amount (Column Q). (The customer # field has many duplicates but the amounts are never duplicates)
On the Reprocessed sheet I have all the rejected items (all fields) and also a field named Reprocessed. I need to use a formula that will check the Paid sheet for any items that have the same Customer # and Amount and return the amount
There are 8,216 rejected items and 45,047 paid items. Some items were originally rejected have been reprocessed and show under paid.
Any thoughts on which formula I should use?
View 10 Replies
View Related
Apr 18, 2008
Is it possible to set up a lookup function with two lookup values? For example, say I have a list of items such as:
1 A 14
1 B 22
2 C 84
4 D 25
I'd like to have the lookup go to the above table and find the number 1 and the letter B and return 22. I can't seem to visualize how to make this work.
View 9 Replies
View Related
Oct 23, 2008
I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.
View 3 Replies
View Related
Jul 29, 2008
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies
View Related
Nov 28, 2006
here is an example....
(this is on a sheet called Summary)
----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649
Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....
---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649
The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).
View 3 Replies
View Related
Jun 12, 2007
I have a very large spreadsheet of customer information(I call it the master spreadsheet). Each row contains only 3 things: Account number, product bought, Price
Later I receive the money from the customer for that product(the pay sheet) that contains the exact same thing in the same order: Account number, Product bought, Price paid.
What I'm trying to do is compare the two spreadsheets so that when i receive the pay sheet of cusomters who have paid with the amount it will deduct it from the master sheet.
So it should compare account numbers when it finds a match then it should subtract the amount paid (column C) from the master spreadheet price column(column C also).
sometimes customers don't pay the right price so it has to be a subtraction so I can see if it was over paied, underpaid etc.
Right now I'm still doing it manually combining the two documents sorting it by account number and checking for matches in column A (account number).
View 5 Replies
View Related
Jan 28, 2009
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies
View Related
Jun 28, 2009
I have a little bit of problem with lookup function. When i paste values from another worksheet (paste special, values) in a cell which is lookup value i get #N/A. These values are numbers. When i put '7 for example i get the values i want from lookup table. I have a lot of these cells and its tedious job to put ' in front of every value. Is there a quicker solution?
View 3 Replies
View Related
Mar 23, 2009
I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
View 2 Replies
View Related
Jul 22, 2011
is it possible to lookup the last 5 values?
I want to lookup letter "A", from a list of 300 parts. "A" may appear 30 times, but I only want to see the last 5 results (Sorted in chronological order).
If I have 5 cells, and perform a VLOOKUP it will always only return the first occurance of "A". I need to find a way of the second cell "forgetting" the first occurance and moving on to the next one and so on
View 8 Replies
View Related
Mar 18, 2007
Can any one tell me out in getting the lookup value.
the file is attached here with.
View 11 Replies
View Related
Oct 20, 2008
I have a spreadsheet with two tables. Table1 (Summary) is my summary data, table2 (Data) is the array of data i need to pull from.
I am trying to create a monthly summary report. The values in table1 include values: date, creative element, revenue. I have both date and creative element defined in table-Data, I want the sum of revenue from each month entered into my summary report. My Data table has multiple values for each date/creative element combination.
Ex of my Data table:
Jan 2008 GoogleBase $1000
Feb 2008 Shopzilla $1500
Jan 2008 GoogleBase $1500
So if my data set was above, the summary of data for Jan 2008 for Googlebase would = $2500.
View 6 Replies
View Related
Apr 22, 2009
See attached w/Note. I would like to return the number in column B that corresponds to the value in column A that is closest to a specified number (input in cell A2).
View 7 Replies
View Related
Jul 5, 2013
A B CD
100 233 a 1
100 454 a 2
100 111 b 3
100 233 b 4
200 345 a 5
200 432 a 6
200 233 b 7
200 333 b 8
300 336 a 9
[code]....
I have values in 4 columns A, B, C & D. I have to get the values from cells in Column D corresponding to maximum & minimum value from column B when the corresponding cells in column A has either "100" or "300" & column C has a value "a". This means, if column A has either "100" or "300" & Column C has "a", then Column B has following corresponding values - 233, 454, 336, 654. Out of this the max. is 654 for which corresponding value in Column D is 10 (First output) & the min. is 233 for which corresponding value in Column D is 1 (Second output). Please note the value 233 & 654 is repeating in "200" & "400" range also.
View 8 Replies
View Related
Apr 16, 2006
I have a table that has 1000 people who have a duration of time associated with them. I need to get the names and times of the top 10 people. I would prefer to use formulas so that I can just make a template that I can paste my data into.
View 9 Replies
View Related
Mar 30, 2014
I am trying to create a table that references data in a much larger report that constantly has rows and columns added and deleted off of it. Here is an example:
---------------Juice Sell In-----Desert Sell in-----Total Sales
James--------34%---------------8%---------------22
Frank---------22%---------------2%---------------18
This is a simple example of what I would be trying look data up from. But there are hundreds or rows and columns in the actual report, so I am creating another document that just pulls the data I need to review off of the larger report. New rows and columns are added to the report on a daily basis. So I am trying to have a cell return a value as a result of an intersection of 2 other values that remain constant. For example: Looking up the intersection of the row that contains "James" and the column that contains "Total Sales" would return "22" as the result in the cell.
View 3 Replies
View Related
Apr 5, 2007
I want to match two different values in the same row and then have it return another value in that row.
View 9 Replies
View Related
Feb 13, 2009
I have a problem with a table I am using I need to look up two values in a table and return the the score to the relative cell.
I have attached an example of what I need. I don't know if I can use the VLOOKUP function or would it be better to use the Index feature?
I have defined a name called 'Com' which is for the whole table in the 'Com' sheet.
On the 'Results' sheet all cells highlighted in Yellow are where values are entered and contains no formula. The Green cells are the cells where I need to amend the formula so that it is more efficient.
What I need exactly is for the formulas in C4 to C11 is to first look up the values in column A and match the value in column B that relates to column A.
For example on the 'Results' sheet, if the value in B6 equals b then the formula in C6 should then look at the table in the 'Com' sheet then match 'Com 1' and then look at the response value in column B to match the rating of 'b' and then return the value of '4' from Cell C2 on the 'Com' sheet.
View 5 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
Jun 8, 2009
I have got a data file. A sample of the data is attached for convenience. Column A contains the name of the books in varioys rows, while column B, C & D contains the qty of the respective books in the given racks.
I want to search by book name likewise :
Qty of Book 1 in Rack1 = 10. Similarly if I want to search for Book2 in Rack3, what should be the formula for it. I have to use a combination of INDEX & MATCH function but somewhoe not able to get it correct.
View 2 Replies
View Related
