Lookup Two Values In A Data Range

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


ADVERTISEMENT

Lookup Between A Range Of Values

Apr 8, 2008

I have a workbook where road structure data is recorded i.e. bridges, culverts, etc.

One of the worksheets has data I need to lookup. The primary worksheet called “Structure_Data” has the ‘Road Number’ (RN) of a particular road and the ‘Road Running Distance’ (RRD) of a particular structure. A particular road may have many structures along its length.

Another worksheet called “Road_Number_of_Lanes” has data that that indicates how many lanes there are between particular RRDs.

Here is what I am trying to work out...
In the “Structure_Data” worksheet look at the RRD of a structure and the number of the road it is on, then look up the road number on the Road_Number_of_Lanes worksheet; look at the ‘Start RRD’ and the ‘End RRD’ and return the number of lanes for that particular RRD.

“Structure_Data” worksheet
Column C has the Road Number
Column X has the Road Running Distance

“Road_Number_of_Lanes” worksheet
Column A has the Road Number
Column C has the Start Road Running Distance
Column D has the End Road Running Distance
Column E has the number of traffic lanes

My problem is that for example Road Number 1000 starts at 0 and goes to 927.09 (approx 927 km long). There are 27 entries of RN 1000 i.e. from 0 to 411.69 there are 2 lanes, from 773.62 to 774.85 there are 3 lanes and from 774.85 to 778.09 it goes back to 2 lanes.

How can I find out how many lanes of traffic there is at a particular structures location.

View 6 Replies View Related

Lookup Range Of Values In Array And Sum If Positive

Mar 21, 2014

I'm trying to define a range of values in two cells M1:M2; lookup that range of values in a three column array (K12:M600) and sum the values in column M12:M600 if they are positive values. I've been trying this formula:

=SUMIF(M12:M600,">0",(VLOOKUP(M1:M2,K12:M600,3,TRUE)))

but cannot seem to get it to work.

View 5 Replies View Related

INDIRECT Function To Lookup Values On A Range Of Other Worksheets

Jan 17, 2014

I am trying to use the INDIRECT function to look up values on a range of other worksheets. I have a column of data in col A which is essentially a lot of different worksheet names. On each worksheet I need to use VLOOKUP to find a value.

Easier to show formulas. This is an example of what I want to recreate:

=VLOOKUP(O2, test!B3:C13,2,FALSE)

I want to replace the text "test" with the text in column A. So the first INDIRECT formula looks like this:

=INDIRECT("VLOOKUP(O2, " &A1& "!B3:C13,2,FALSE)")

Which to me looks like it should work but I get a #REF! error. I've tried various permutations, e.g. having the INDIRECT part of the formula located in a different place (next to the "test" text) but run in to similar issues.

View 5 Replies View Related

VBA - Lookup Value Found In A Column Within Another Range And Copy Adjacent Values

Jan 30, 2014

I'm trying to create a macro that can lookup values down a list, find that value within another list and copy the adjacent cells.

The values to look for will always be on the same column and the values to look into will always be in the same range or columns but not always in the same row.

For example.

AI:AI contains a list of ID's which will be manually input every day so they might have a different order.

C:AF has all the data to look into. C always being a list of ID values and I want for every ID found on AI:AI to be search for on C:C: and then once a match is found, I need specific adjancent cell values to be copied next to the ID found on AI.

The values that need to be returned are found on columns B,D,F,G,H,I,J,P,AF and they will always display on those columns.

I've been doing this based on vlookup and INDEX/MATCh formulas but I've come across some limitations for something I want to develop further so I'm trying to do this based on VBA.

View 5 Replies View Related

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 View Related

Lookup Data With Multiple Values

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

How Do I Lookup Data With Two Comparison Values

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

Return Offset Data/values With Lookup

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

How To Lookup Range Of Data In One Cell

Jan 6, 2014

how to lookup some of my "range of data" in one cell.. please have a look at my sample workbook..Book.xlsx

View 7 Replies View Related

Lookup Values From A Multi Dimensional Table / Data Sheet

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

Lookup Data From Validation Drop Down And Return Multiple Corresponding Values

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

Lookup Data Within Data Range And 1 Other Criteria?

Jul 29, 2012

i have a treatment centre with 22 rooms some with mutiple beds, i need to view occupancy in a grid form to optimise bed useage. i need to look up a date that is after arrival but before depature that matches a room number, i have tried match, index, and, vlookup etc but all this info must exist on the same row

View 1 Replies View Related

Lookup 2 Values: Get The Lookup Answer Based On Two Values

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

Multiple Lookup Values Rows And Columns To Lookup Single Target Column On Right End?

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

Link To External Source Workbook For Data Validation Lookup Values Returns Zeros

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

Lookup Formula With Two Lookup Values

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

Set Up A Lookup Function With Two Lookup Values?

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

Lookup All Values In Ascending Order And Return All Corresponding Values.

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

Lookup Range Of Cells And Populate Specific Cells Based On Matching Data?

May 23, 2014

I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.

I have attached the worksheet so you can see what i am trying to achieve.

View 2 Replies View Related

Hiding Zero Values From The Chart Data Range

Jul 7, 2014

I am currently building a tool that works with an undefined range of input variables, e.g. I now have a range of 200 values but it could just as easily be 400 or 100. I am using these values for further calculations and have thus 'dragged down' to cells that in some instances are empty, resulting in values that are 0. I'm also building a chart based on these values, but it shows the values that are 0 as well!

Is there any way to just plot the non-zero values without changing the data range?

View 1 Replies View Related

Generate Test Data From Range Of Values Using Vba?

Apr 15, 2014

I want to randomize a range of values stored in sheet 1 and insert them in another cell.

I am having values for sales consisting of product names and its price. there are 10 product names and its corresponding prices and is stored in cells E1:F10. I want to generate test data containing product names and its corresponding price. The generated data needs to be saved in the cells A1:B50

the product names needs to be randomized. This needs to be done in vba. Below is the screenshot of the final result that is needed. As you can see, I have randomized my 10 products and its prices into the test data column. This was done using vlookup formula and I need the same to be done in vba.

View 2 Replies View Related

Display Values From Range Of Rows That Has Data In It

Mar 10, 2014

I am not very smart in Excel. I have problem with pulling data from the following range of rows.

A
1 Apple
2 Banana
3
4 Grapes
5
6 Pomegranates

Expected Result: Apple, Banana, Grapes and Pomegranates

scenario: One or more than one of any of the 6 rows can be blank. I need a formula or procedure that pulls together data from A1:A6 automatically and adds "and" between the last two values. See the expected result above for an example.

View 9 Replies View Related

Selecting The 2 Last Values In A Column Of Data With Range

Jan 23, 2007

I m trying to make a button to add values to another sheet in my xls. Ive done that... now i try to autofill the percentages from left and above one row....

View 9 Replies View Related

Lookup Function To Lookup For Data In Another Table

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

Lookup Data Left Of Found Lookup Value

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

Lookup Matching Data & Subtract From Another Lookup

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

VBA To Execute When Data Values Copied / Pasted Into Range Of Cells

Jan 27, 2014

Below is code I have attempted. I am learning VBA and have gathered this code from the internet and this forum. I have data that is in columns B to E and from row 7 down. I want this VBA code to run when data is copied/pasted in these cells. Where the last values stop will vary so I just want to be sure this code is executed after all the data is pasted in these cells. The data will be pasted at one time. When the user removes the data from these cells, I do not want the code to run.

Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if less than four cells are changed or content deleted
If Target.Cells.Count < 4 Or IsEmpty(Target) Then Exit Sub

[Code].....

View 1 Replies View Related

Calculate Average For Values In Range Of Data That Fulfill Certain Condition

May 20, 2014

I am trying to calculate average for values in a range of data that fulfill a certain condition viz. >1000 &

View 5 Replies View Related

Check If Values Exist In Range And Move Them To Another Workbook With Additional Data

Jun 9, 2014

I have 400 source files containing (among others) 8 sheets with daily results: "Fri 23", "Mon 26", "Tue 27", "Wed 28", "Thu 29", "Fri 30", "Sat 31 (if applicable)", "Mon 2".

Each sheet contains also:
State - D1
Role - D2
Staff ID - D3
Date - D4

Activity group name in column A (starting from row 8)
Activity type in column B (merged with C and D) (starting from row 8)
Activity time in columns E:GV (starting from row 8). Usually, there is none or only one value in whole range (e.g. E8:GV8). But sometimes there are two values.

Customer ID in row 6 (value appears only if time was reported in E:GV range)
CC Number in row 7 (value appears only if time was reported in E:GV range)

It's all about transferring values from all daily sheets in all files (.xls) sitting in folder C:WADFinal to one simple table (WAD_Consolidation_file.xls, sheet "Consolidated") consisted of 9 columns: Staff ID, Role, State, Date, Activity Group, Activity Type, Minutes, Customer ID, CC Number.

Additional note if two values exist in the same row they should be copied as two separate entries to consolidation file.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved