Tracker
HOME    TRACKER    Excel

# Lookup Values Along An X And Y Axis

## I am not sure of the best way to ask how to do this. I am trying to efficiently lookup the X value in sheet A from the Y value in sheet B programatically as X=Y. The Values along the X and Y axis of sheet A change. Sheet A...............................................

Related Forum Messages:
Changing Values Of X-axis, And Descending Order Axis(x-axis)
Two questions:

1) How do make values on x-axis as words? E.g. apples, oranges, etc.

2) Is it possible to have the numbers on x-axis in descending order? If I can figure out 1), I can do this as well, but it would be nice to know if it is possible to have the numbers on x-axis in descending order.

Lookup On 2 Axis
I have a table as attached. What I have is 2 tabs. Data - is the raw data which shows the date and time of each application per person and the time cut and formatted for lookups. Sheet 1 - This is a rota showing each member of staff.
Below the rota is a definition of each shift, i.e E = Early which is 8.30 to 5.00pm.

1.
At the bottom of sheet 1, I would like the total of each application for each member of staff for the time they have worked for each day.

i.e if staff member N-R worked a late (L) on the 2nd, I would like the number of application taken on the 2nd for N-R between the hours of 10.30am and 19.00.

I have tried nesting a vlookup and a hlookup function but I just cannot fathom it out. I have also tried to use coutifs().

OR

2.
On the data sheet I would like an additional column after the cut time to flag up applications taken for each member of staff during their shift. i.e if N-R takes 5 application on 22/12/2010 between 8.30 and 12.30, 3 after 10.30. I would like each individual app to flag up in its row if taken after 10.30 for this member of staff on this day.

Summing Matrix Values Based On X & Y Axis Values
The simplified example below is intended to calculate the sums from the data values in the upper data set and place them in the appropriate cells in the lower summary table. Example......

I would like to find a single-cell formula that will handle the multiple instances of either the X or Y headings (ex: there are two 'Yes' Rows) and still reach the appropriate summations. I would like to accomplish this without resorting to Macros or manual cell references if possible. I've acheived some limited success with array formulae so far, but find that the multiple instances of 'Yes' cause only one row to be summed before the formula finishes.

Limiting Values Of Y-Axis In Charts?
Is there a way to limit the range of values on the Y-Axis on the lower end? Like I don't wish the Y-axis to begin from 0 but say 500. Is that possible?

I have a set of data values ranging from 650 - 850, but the Excel graph however looks too fine because the Y-axis begins from 0.

Draw Log Chart Or Edit Axis Intervals And Starting Axis Value
I have some numeric data that has to be put on a log chart. but there is no option for log charts. so i converted my data to log and then drew a chart but i am unable to edit the axis intervals and starting axis value. whenever i change the value it gets back to its default value,ie 1. can anyone tell me how to draw log chart or how to edit axis intervals and starting axis value?

Chart - Mark Y-Axis With Values From Table
is does anyone know if there is a way of marking the y-axis in a chart with data from a table, so rather than having a fixed segmentation width have the distances between the data points marked on the Vertical to conform to a range in a spreadsheet? I know you can do that for the x-axis. Is there any way to do that for the y-axis as well?

Hide Minus Values On Y-axis On A Chart
column chart displaying various pieces of data.

At the top of my worksheet I have a combo box which allows you to select a local authority name; and all relevant information in the worksheet is highlighted (using conditional formatting, extra series in charts to make relevant points highlight in red, etc).

I've added a series to the chart and changed it's type to line - then with the data labels positioned beneath the points on the line I can get conditional formatted x category labels - all outlined here: [url]

Because my x category labels are fairly long I've had to give them an orientation of -90 and make the minimum y-axis value equal to -200 to give enough room beneath the chart to fit the text in.

As I said above, the values below 0 are just there to provide space at the bottom of the chart, so my question is: is there a way to hide these negative values? I'm sure I've done it before, but can't remember how - I thought maybe a custom number format of #;[White]# would work, but it just inverted my text (white text on black background).

Second Category Axis & Value Axis To Current Chart
how to incorporate another category and value axis to my existing chart.

1. I need to add a rank axis derived from my data column (C6-C15). This column should rank from highest to lowest and create a tie if any number is repeated. I have arranged the rank column in cells B38:L38 as they would appear. I would like this to appear on the top or bottom of the bar graphs.

2. Second I need to add the frequency of hits which = 3 derived from cell J1 This should appear on the chart were it is currently titled. This should create another bar graph colored green and labeled 3.

3. Lastly the numbers axis can remain the same as it appears on the chart or if it would be better to align as shown in cells B43:L43.

Maximum & Minimum Axis Values From Chart
how I get the maximum and minimum values of the axis from and excel chart? What I mean is I want to specify a chart, and output in the same excel worksheet the values for the maximum and minimum of the two axis.

I have tried various searches, but cannot come up with the answer, although I have a sneaky suspicion it in here somewhere.

Setting Y-Axis Min & Max According To Worksheet Values
i want to set the minimum to be 20 less than the minimum value in the data, and the max to 20 more than the max value of the data.

Horizontal And Vertical Chart Axis As Value Axis
What is the best way to have both horizontal and vertical axis as value axis?

Lookup 2 Values: Get The Lookup Answer Based On Two Values
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?

Set Up A Lookup Function With Two Lookup Values?
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.

Lookup Formula With Two Lookup Values
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?

Lookup All Values In Ascending Order And Return All Corresponding Values.
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.

Paste Values From Another Worksheet (paste Special, Values) In A Cell Which Is Lookup Value I Get #N/A
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?

IF Or LOOKUP: Search For Values From One Worksheet And Identify Whether Or Not Those Values Exist In Another Worksheet
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.

Lookup Between Two Values
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).

Getting The Lookup Values
Can any one tell me out in getting the lookup value.
the file is attached here with.

Lookup 2 Values To Return 1 Value
Hi there, please can you help me transfer data from 1 spreadsheet to another (I have attached a file which has 2 sheets).

I am trying to populate columns G to L (sheet B) with data from column H (sheet A) - the lookup is between 'Material' & '7410','7090','7680','7060','8630','7580' on sheet B & material & plant on sheet A.

I have tried H&VLOOKUP & INDEX/MATCH but I keep returning a #N/A..

Lookup With Repeating Values
I have a spreadsheet in which data is dumped from a database, there is a dump (on a new tab) for each site.

I need a summary sheet which will add up the two tabs. My problem is however is that I can not use Vlookup as the same codes are used and it will only return the first occurrence.

e.g.
444588 Prime Wages/salaries
This code appears under Production prime labour and Distribution Labour but will only ever return the production prime labour value

Another example would be
444586 Ohd Wages & salaries

Is there any way that I can get the summary sheet to use a formula where it looks up the department and then the code? I was thinking index and match but I was unsure if this was possible also.

Worth noting is that the summary sheet has a list of all codes that could be used but the dumps will only ever have values if a cost has been occurred, there for the number of rows in each department is not consistent. There will be a number of tabs however in the attached example I have only provided two.

Two Lookup Values, Vlookup
I want to match two different values in the same row and then have it return another value in that row.

Vlookup Using Two Lookup Values
Is there a way to perform a vlookup with two lookup values?

I'd like to look up the value of A1 and B1 in a table with A1 and B1 values
included to return cell C2 from the table. I've had limited
succcess using concatenate and then using vlookup on that cell but i'd like
to not have to do the concatenate step.

LookUp Values And Sum
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:

Feb 2008 Shopzilla \$1500

So if my data set was above, the summary of data for Jan 2008 for Googlebase would = \$2500.

Lookup 2 Values In A Table
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.

Lookup For Top 10 Values
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.

Lookup Values In Two Different Sheets
I have two worksheets with different data in them. In worksheet "Sheet1"

A B C (?)
1 01 03
2 01 03
3 01 03
4 03 03
5 02 04
...

In worksheeet "Sheet2"

A B C
1 01 03 Mechanical Engineering
2 01 04 Aerospace Engineering
3 02 03 Electrical Engineering
4 02 04 Electronic Engineering
5 03 03 Hospitality

What i would like to do is to fill in the C column in Sheet1 by checking if the value for A and B columns match up with those in Sheet2, then copy the value in C column and put into C column in sheet1. for example if A1 = 01 and B1 = 03, then it is Mechanical Engineering. I was thinking of using VLOOKUP but don't think it'd take "if else" criteria.

Double Lookup Values
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.

Using Two Lookup Values In One Formula
I am trying to calculate a value by using two lookup statements in one formula. However, I get either #N/A, or very wrong results. Here is the formula:

=LOOKUP(\$B26,GrapePricing!\$A\$2:\$A\$15,GrapePricing!\$B\$2:\$B\$15)*
LOOKUP(\$B26,\$B\$5:\$B\$17,\$D\$5:\$D\$17)

What I need is to get the value in B26 (it is text, "Merlot"), go to the GrapePriceing sheet and find Merlot in range A2:A15, get the price of Merlot from the GrapePricing sheet range B2:B15, then multiply that by the matching value in the D5:D17 range.

Lookup Values Columnwise
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.

Vlookup With Two Lookup Values
=VLOOKUP(C29,'Zero Days YTD'!B:Q,16,FALSE) is my current formula where C29 = Jack Bates.
As of this month Jack Bates is now in two places on worksheet Zero Days YTD. One is named "Jack Bates (from AMU 3/1/07)" and the other named "Jack Bates (to APU 3/1/07)"

I tried =VLOOKUP("Jack Bates (from AMU 3/1/07)"&"Jack Bates (to APU 3/1/07)",'Zero Days YTD'!B:Q,16,FALSE), but that didn't work.

Lookup Between A Range Of Values
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

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.

Lookup And Return Ascending Values
I have a column of values for example:
114
162
92
114
120
120
114
162

I need to be able to search this column and return the first instance only of each number in a different column in numerical order - which in this example will result:
92
114
120
162

Match And Index With Two Lookup Values
I'm trying to do in Excel 2000 that seemed way easier before I tried to write the formula. Any thoughts would be much appreciated.

I have some data setup more or less like this:

1State DeptJan Feb Mar
2California 50128 31 6
3Utah 20119 3 11
4California 20122 49 22
5New York 301 29 51 15
I'm looking to write a formula to retrieve the numerical value located in the cell for a particular month, across from a particular department number and state - so I can plug it into another spreadsheet.

I tried the following formula for January, but I get an "N/A" error when I hit 'Ctrl+Shift+Enter' to use it...

=INDEX(C2:C5,MATCH(1,("Utah"=A2:A5)*("201"=B2:B5),0))
Can you not use static values in this type of formula?

V-lookup To Find Several Different Values And Then Sum Them
Can you use a V-lookup to find several different values and then sum them?

Lookup Formula - Missing Values
The formula works well 50% of the time, but randomly skips over some values. I have attached a sample of what I'm working on.

Extracting example.xlsx

There will be thousands of questions similar, but think there will be a limit of 4 numbers to be taken out.

Lookup Based On Two Unique Values
I've been searching both here and on google but I've been unable to make use of any of the numerous suggestions and examples I've found to do this; given I'm right in thinking that this should be done with a vlookup!

I have a table in which different customers pay different prices for different products. C13 is a cell in which I want Excel to look up the correct value based on A13 and B13 (in this case C3).

I have no experience with lookup functions, which might be the reason why I can't get this to work. I bet it's pretty simple

Lookup That Returns Multiple Values
I'm trying to do the next level of a v-lookup. I have a group of data with a text "flag" on certain rows. On my summary worksheet, I'm trying to do a lookup or a nested index function retrun all of the values with the text flag next to it.

I've been playing around with nested formulas and this is where I'm stuck. Here's what I came up with:

=INDEX(\$D\$1:\$E\$9,(SMALL(IF(\$D\$1:\$D\$9=\$A\$1,ROW(\$D\$1:\$D\$9),ROW(1:1)),ROW(1:1))),2)

This only works for one row and not for the other rows. Maybe I'm going down the wrong path?

Transfer Lookup Values Between Workbooks
I have two workbooks with the same sheet names.
One workbook is called "Main" and the other is called "Subset".

Column D in each sheet is mostly identical between the two workbooks and contains the LOOKUP references.

I would need a macro (called from the "Main" workbook) which allows the user to browse for the "Subset" workbook.

Once selected, the macro should cycle through all sheets of the "Subset" workbook and for column D values LOOK them UP in "Main" workbook and transfer the adjacent values from column C of the "Subset" workbook to column C of the "Main" workbook.

Appropriate error handlers need to be in place for:

- Selecting the appropriate workbook (i.e. one which contains identical sheet names)
- The "Main" workbook is likely to contain additional sheets which should be ignored by the macro
- Column D LOOKUP entries which are not found in the "Main" workbooki should be ignored (and vice-versa)

How Do I Lookup Data With Two Comparison Values
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.

Lookup To Return Multiple Values
Is there a way to use a lookup formula to return all values of a given text, not just the value of the first listing of the text. In an attempt to clarify my already confusing request, if A5 on "worksheet1" is "Land" I want E5 on "worksheet1" to return the summation of values assigned to "Land" "worksheet2" "column B" any time "Land" appears on "Worksheet2" "Column A".

Lookup Data Between Set Values
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.

Lookup Data With Multiple Values
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.

Lookup Date Value Based On 3 Values
I have data that contains all the days in a year G7:I372. I want to lookup a value based on month, date and return the result. The sheet I am using contains a list in A3. As I change the value of A3, i want the dates to update. See attached sheet.

Lookup Several Values Based On Same Text
I want to sum all expenses on Company B within Expense 1. Is it possible to use MATCH/INDEX or something else to do that? I can't use SUMIF, because I don't want to include Company B within Expense 2 for instance.

Lookup For Vertical And Horizontal Corresponding Values.
I have a problem that lookup vertical and horizontal corresponding values when there was duplicate values as it's only returning the first value found. What I want was to lookup the vertical and horizontal corresponding values on the left most & top most column based on the largest values column and also to return the duplicate values under the vertical and horizontal value column in ascending order if it's a duplicate values.

Lookup Single Value In Cells With 2 Values
I have following sample list of data:

PostalCodesCountryTerminal
0000 - 1069BEGrimb
1100 - 1179BEGrimb
1070 - 1099BETern
1020 - 1229ATLZN

I now need to determine the Terminal for postalcode 1086 in BE
but since the postal code that I need to find is within a range
(1070-1099) that is within a single cell, and there is a second
argument (BE) in the next column, my novice knowledge of VLOOKUP()
isn't sufficient to come to a result

Concatenate Unique Values After Lookup
I am trying to come up with a user defined function to accomplish several things at once.

First, I need to lookup a reference value in one column and determine the value from another column (on the same worksheet) in the same row. Then, concatenate each "return" value (that isn't blank).

I have the following code so far, but my formula results in a zero. If I remove the On Error Resume Next, the formula results in a #VALUE error.

Public Function ConcatUnique(Separator As String, Ref As Variant, LkupCol As Range, _
RetCol As Range)
Dim lkup As Range
Dim ret As Range
Dim colDif As Long
Dim mCollect As New Collection
Dim i As Integer
Dim b As Variant
' Determine the number of columns difference between
' the lookup column and the return value column.
colDif = RetCol.Column - LkupCol.Column
On Error Resume Next
' Determine which lookup values in the lookup range match the reference value.
' When the lookup value matches the reference value, set the return range object
' to the cell in the return range (column) in the same row as the lookup value.
' Note we use the difference between the lookup column and the return column to
' determine the location of the return range object.
For Each lkup In LkupCol
If lkup.Value = Ref.Value Then Set ret = Range(Cells(lkup.Row, lkup.Column _
+ colDif))
' Store the return value in the collection object. Ignore any blank return
' values. Note we use the range value converted to a string as the key
' value.
If ret.Value <> "" Then mCollect.Add ret.Value, CStr(ret.Value)
' Loop through each cell in the lookup column range.
Next lkup
' Write each item from the collection and the separator to the final result,
' writing each value and the separator after the previous value and separator.
For i = 1 To mCollect.Count
b = b & mCollect(i) & Separator
Next i
ConcatUnique = Left\$(b, Len(b) - Len(Separator))
End Function

Lookup Values Less/Greater Than Set Value
I am using vlookup to read a 3500 from the table below.

"Vd =65mph
R(ft)"e
126001.5
91302.0
82002.2
74302.4
67702.6
62002.8
57103.0
52803.2
48903.4
45403.6.......

My question is how do I write the formula so that it looks up a radius of 3500 by looking up a value slightly smaller than 3500, in this case 3440 and return the value of 4.4

Looking Up Multiple Values In A Lookup Table
I have a giant lookup table with five columns. I have three variables, and I need to find the row in the lookup table where the variables match the first three columns of the lookup table. ie. Find the row where variable 1 matches the value in column 1, variable 2 matches the value in column 2 and variable 3 matches the value in column 3. If this row exists then return the value in columns 4 and 5 of that row. Otherwise, return a zero.