Lookup 2 Values In A Table
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
ADVERTISEMENT
Mar 12, 2013
I have the z-table imported into excel, How i can look-up values from the table to 2 d.p
For example, to lookup 0.49 would be the intersection of 0.40 down the rows and 0.09 across the columns, i.e a value of 0.6879
anoda example would be 1.26 which would be 1.20 down the rows intersecting with 0.06 across the columns and it gives a value of 0.8962
I have tried some index-match but hasnt worked out fine, i know excel's normsdist function can do this but i need to get it done this way
0
0.01
0.02
0.03
0.04
0.05
0.06
0.07
0.08
0.09
[code]....
View 2 Replies
View Related
Jul 4, 2006
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.
View 4 Replies
View Related
May 3, 2009
I have a sheet, called "output", in which I need to complete column C "calculated values". I need to complete the table based upon a formula table (which is in sheet "formulas").
For the first row of data, cell C2, I need to take the price per order ($0.25; cell A2) and number of orders (40; cell B2) and copy data to cell B4 and B6, respectively.
Once the data has been copied to to cells B4 and B6 on the fomulas sheet, I need to copy the calculated value in Row N to the output sheet. Note that the value being copied from N can be N11, N12, N13, N14, or N15 (the one that is <> to null).
View 2 Replies
View Related
Jan 5, 2007
the formular that i have in the attached file is returing all the customer numbers
Is it possible to return only one value of each
the data is in the "data" sheet and the formulars are in sheet1
View 3 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
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
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
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
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
Jun 5, 2014
I'm trying to see if you can look up multi columns for a number and when it finds it return back to one column and return that data?
I am trying to sort out territories for a state I work on; the territories are divided up by zip codes.
i.e.
A B C D
territory 1 12345 54321 11222
territory 2 22222 33333
territory 3 44444 55555
and what I want is to say put a formula in cell B3 on Sheet 1 pointing to cell C3 that has zip code 33333. I want it to then take that and look in Sheet 2 and search A1:D3 and when it finds 33333, return "territory 2" back to cell B3 on Sheet 1.
View 3 Replies
View Related
May 1, 2007
I need to place a lookup table in a work book and I'm not sure how to do it.Below is what I specifically need in my workbook.
c. The workbook will need a lookup table that will lookup the tuition, clothing
and entertainment figures depending on the selection of college, and will
ensure that only the colleges on the list are selectable. That is, the
worksheet will not allow the user to enter another college not in the list.
The lookup list must be on a worksheet by itself at the end of the workbook.
View 13 Replies
View Related
Aug 4, 2009
I am attempting to do a search on a 2 dimensional table, based on Product Code/Month. Is there a function, addin or a macro that will allow me to do this? All I can come up with is a messy combination of VLOOKUP/HLOOKUP/COLUMN.
View 2 Replies
View Related
Nov 23, 2011
I get a file every day that contains online orders for a client. They have made the state and country fields on the order form free text, so sometimes the State field has CA, sometimes California, sometimes even Cal or Cali. Oregon may be represented as OR, Oregon, or Ore (and one time even Orygon.)
The country field may have US, USA, United States and sometimes United States of America.
Well, they need this data imported to their system with the states fully spelled out: Oregon, California. And the country spelled out as well: United States, Canada.
Right now I have a lookup tables worksheet that I just have a macro run a vlookup on. But I'd like to know if there is a way I could just put it all in the code, without the external tables.
i.e. some code that, in English, would basically say, "If the value of any cell in column K is among these values, replace it with those values, otherwise just leave it as it is". Could I do that while containing the two sets of values right in the code rather than on a worksheet?
I really just want to replace the standard abbreviated state/country codes with the full text, and if the value does contain Cali or Ore, let them manually make the change. Hopefully that will finally nudge them to just make the online order form utilize a drop-down list of states and countries, rather than free text.
View 1 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 15, 2014
I have a large table that I want to reconstruct. For simplicity sake, let's just says it's 3 rows (excluding headers) by 3 columns.
Item Description
1/1/2014
1/2/2014
1/3/2014
Cheese Burgers
2
3
4
Hot Dogs
5
12
6
Beverages
2
5
3
I want to reconstruct it so that the column headers become values in the table. The table headers are dates, in this case, if that gives clearer picture. So the new table would have 9 rows, (3 rows of data, excluding the header times four columns).
Item Description
Date
Quantity
Cheese Burgers
1/1/2014
2
Hot Dogs
1/1/2014
5
[Code] ....
The above example is sorted by date but I would be indifferent if it's sorted by the Item Description.
Is there an easy way to do this? Pivot possibly? Again, my data table is large: 36 rows x 181 columns. Using the copy/paste/transpose feature is pretty impractical.
View 3 Replies
View Related
Jul 4, 2009
I have a table of this format:
d e f
a 1 2 3
b 4 5 6
c 7 8 9
Now i want to find the column title for a particular value in the table. For ex, corresponding to row 'b' and value '5', how to get 'e'??
View 3 Replies
View Related
Sep 27, 2009
I have a large set of frequency data obtained from experimental testing which is dependant on three variables - a span length (from 0-20 metres), vehicle type (divided into motorbikes, cars & trucks - small, medium & large for each) & speed (20-80 km/hr).
Given a span length, speed & particular vehicle (eg large car), I need a program/formula that calculates the equivalent vehicle type (eg in 'motorbikes' - it would correspond to 'small') based on the frequency data. The equivalent vehicle frequency would be +/- say 0.5 Hz (since one type of vehicle would not have the exact same frequency as another).
I have tried experimenting with 'vlookup' functions but it doesn't seem to yield a solution to this problem.
View 14 Replies
View Related
Aug 7, 2009
I have a spredsheet for a weight lifting contest they are using the wilks formula to give each lifter a coefficient. I have them all in a table and we usually just look them up manually. This gets us very behind with the fast paced nature of a live contest. Here is a sample of what I have a data.....
MaleBWT00.10.20.30.40.50.60.70.80.9401.33541.33111.32681.32251.31821.3141.30981.30571.30161.2975411.29341.28941.28541.28141.27751.27361.26971.26581.2621.2582421.25451.25071.2471.24331.23971.2361.23241.22891.22531.2218431.21831.21481.21131.20791.20451.20111.19781.19441.19111.1878441.18461.18131.17811.17491.17171.16861.16541.16231.15921.1562451.15311.15011.14711.14411.14111.13821.13521.13231.12941.1266461.12371.12091.11811.11531.11251.10971.1071.10421.10151.0988
FemaleBWT00.10.20.30.40.50.60.70.80.9401.49361.49151.48941.48721.48511.4831.48091.47881.47661.4745411.49361.49151.48941.48721.48511.4831.48091.47881.47661.4745421.47241.47021.46811.4661.46381.46171.45951.45741.45521.4531431.4511.44881.44671.44451.44241.44021.43811.43591.43381.4316441.42951.42731.42521.42311.42091.41881.41661.41451.41231.4102
If the lifter is male and weighs 46.2 kg her gets a coefficient of 1.1181 or female of 44 KG gets on of 1.4295
My question is how can i get this info automatically from my wilkes worksheet over to mt deadlift page?
My dead lift page looks like this.. I'm trying to fill in the wilks fields automatically by using the bodyweight field that we fill in when the guys come in to lift.
Best LiftWilkesTotalBody Weight KGBody Weight LB0#N/A#N/A0.0000.0000#N/A#N/A0.0000#N/A#N/A0.000
View 9 Replies
View Related
Oct 21, 2013
I am trying to build a formula to do a look up from pivot table. Error in the formula.
View 13 Replies
View Related
May 22, 2014
I am looking for how to find a closest match for to a a given number in a table based on looking up the value of one cell to determine what column to look in, then compare the given number to the values in the column, and then return the value in a another column of the same row it finds the match in. Ive attached an d example.
The value in B2 is the value I am trying to find a closest match to. It first must determine which column to look in, and that is dictated by cell B3. So, since R-404a is entered in B3, column J is the column it looks in, and it returns 0.21, since it is the closest match .
View 3 Replies
View Related
May 15, 2014
A b c d
100 100 x
100 y
100 z
I want to lookup 100 in colum b with a result of z instead of x in criteria c:d
View 2 Replies
View Related
Jul 28, 2014
I need to make a program to take inventory at a bakery. We do this twice a day. Our products are loaves of bread. We have white, multigrain, soy and lindseed, low gi, and gluten free.
They are in pallets, trolleys, crates, boxes, pallet rows (part of a pallet) and individual loaves (singles)
I need to find out how many loaves that we have. How do I total them up?
View 3 Replies
View Related
Apr 7, 2009
I'm currently using a lookup table to determine programs to run on a machine, however I have 3 exceptions to this and can not get my code to work.
Currently the code looks at column D for the type, then column F for the thickness of material. It then calculates the etch time required using the rate (all shown as 1 currently) on the cal_sheet and then rounds this up to a whole number. This is then compared to a second table where the program details are listed against etch times.
View 7 Replies
View Related
Dec 5, 2008
the code for lookup value of point 1 and point2 from the table ,if i key in the no.group and type of grade.
eg.1 Group10 grade a value to be point1=75 point2=20
eg.2 Group16 grade B value to be point1=125 point2=172
View 3 Replies
View Related
Jun 23, 2013
How to do simple table lookup. I have a table showing #of colors being used for screen printing down one side and qty of shirts ordered going across the top. I would like a formula that when I enter the # of colors and the quantity of shirts it will return the correct price.
12-35
36-71
72-143
1
1.50
1.25
.90
2
2.00
1.50
1.10
3
2.75
1.75
1.30
View 2 Replies
View Related
Aug 17, 2013
I have an engineering spec table. Down 12 columns I have height in metres of a post and across the rows I have square metres with 5, 10, 15, 20 and 25 being the sizes.
The engineering spec of the post type is in the table. There are some duplication of post types so post heights of 1m, 2.1m, 2.4m, 2.7m, 3m, 3.3m and 3.6m are the same, then they change. Its a similar thing in the other column headers for 10sq.m 15sq.m etc.
I need a formula so that if the post height of 3m was chosen and the sq.metres was 15 then it would tell me the post type from the table. Or if the post height was 4.8m and sq.metres was 25 it would tell me.
The post type is an engineering code of numbers and letters with no spaces.
View 5 Replies
View Related
Oct 9, 2006
I use a vlookup function on some database to lookup some data. However the data that I wanted to lookup is actually on the third row. In my attached sample, I have some data in "source data" worksheet. Another worksheet "Ratio" is where I keep the lookup value. In this sample, I actually wanted to lookup the ratio of 0.7 for Geogetown. And 0.66 for Queenstown. Could the vlookup formula be modified or need some vba code to achieve this?
View 5 Replies
View Related
