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 RepliesI 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.
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.
If you have a data in two dimensional table. and you need to fill each cell with the value returned from another table.
Example : this the my table
Company A Compay B Company C Product 1
Product 2
Product 3
Product 4
and this my data :
Products Companies Price
Product 1 Company 8 Product 1 Company A 7 Product 1 Company B 5 Product 1 Company C 6 Product 2 Company B 6 Product 2 Company C 9 Product 3 Company A 10 Product 3 Company B 8.8
i have a problem with my report.
there is a dimensional table:
DIM RESULTSDIM_111.1DIM_111.8DIM_111.2DIM_25.3DIM_25.5DIM_380.2DIM_380.1DIM_380.35
i need to make a new table in wich all "dim" will repeat only once and the results will be the average of the results that belongs to the same "dim"
the times that the "dim" repeats can change and the "dims" could be not only till 3
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.
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.
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 RelatedI 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 RelatedI have a simple(?) problem but for some reason I can't find solution for it...
I just need to find if a value exists in a table.
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.
I have a table comprised of 52 weeks (7 days each week) totaling 365 days. Here's what one week only
looks like
Week 2
Sun.
Mon.
Tue.
Weds.
[Code]....
I keep the prior year in a separate sheet in this same workbook. I need a formula or function or something to search the prior years table and pull the last years sales and last years customer count from the prior years sheet based upon the date.
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'??
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.
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
0000AAAAABBBBBCCCCCDDDDD
1111SugarRaisinDirt
2222MilkWaterWaterFlour
3333FlourSugarDirt
4444EggsWaterFlourSugar
5555WaterMilkDirt
I looked for a way to represent my data in a cleaner way than this but couldn't find any tools....a point in the right direction would be helpful. I guess the old plug-in that converted Excel->HTML isn't available?
Column A is a finished product and B-D are the ingredients to make it however values in Column A are also ingredients (sometimes multiple times). I want a formula that searches for values in Column A within the table B1:D5 and returns an array, if possible, of the finished products where Column A was used.
For example, searching for Sugar would return "Flour, Water". Searching for Water would return "Milk, Eggs" (I'd rather not have Milk listed twice but beggars can't be choosers).
I have a table which details an applicable fee given a level of performance. However the levels of performance can be any value and the fee level table which i need to look up gives conditional ranges such as if (out performance is) >4.5% and <5.00% then applicable fee will be 0.7%. However my table is large enough that using a nested IF formula seems unfeasable. I've attached a sample of what i mean which explains it better.
View 2 Replies View RelatedI am trying to build a formula to do a look up from pivot table. Error in the formula.
View 13 Replies View RelatedI 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 .
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
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?
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.
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.
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
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
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.
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 RelatedI have generated a report from my system that shows which customer bought which item # and its qty. There are approx. at least 500 item/customer to go though, is it possible for me to do vlookup or other formula into sheet 2 that will show the qty # for each customer? (pix #1 into pix #2- possible?)
Pix #1:
http://img176.imageshack.us/img176/2606/01km1.jpg
into this table...
Pix #2:
http://img140.imageshack.us/img140/6250/02ah1.jpg
- If I need to attach my working file.
In my enclosed attachment, you will see my dilemma. I have a list sort based on the cell value, but when two values are the same, it shows the first alphabetical listing twice when I INDEX. I can go into the individual cell and correct it, but how can I apply the formula to all the cells so no matter the matching values, I always get name A and B, rather than two A's, for example?
View 3 Replies View RelatedI am having a littler trouble with using a function to find text (last name) in a table and then return full name from the table. See attached sheet with example of what I am looking for.
A quick explanation:
Have a table with columns that have team and name of player. My input is the last name of the player and the team. Need a fomula that searches all the table and then returns the full name of player based on 2 conditions of last name and team.