Lookup When Value Not In 1st Column Of Table
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
ADVERTISEMENT
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
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
Jan 8, 2009
I would like to do a vlookup where the lookup value isn't in the first column of a range/table. Would I use index/match? For example, I have fields for vendor, part # and location in that order. I'd like to pull up location with a formula based on part #.
View 4 Replies
View Related
May 16, 2013
I'd like a formula that'll return the column header by matching a lookup value with a table in the second sheet.
eg: sheet 1
Name
Cell
Region
John
111-2222
[Code] .......
The formula should match the name in A2, John, with value from the table in sheet 2 and return the correct region, this case North.
View 1 Replies
View Related
Mar 5, 2014
I am having trouble with my macro. I have attached excel sheet for reference.
Basically, I want a Macro that Looks up columns B & C of current sheet and matches it with table in the charges tab, and then returns hourly pay in column D.
I have created the macro but it's giving me Run time 1004 error.
Also, my formula is incorrect. Maybe that's why my macro isn't working?
2WayLookup_Macro.xlsm
View 4 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
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
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
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
Jan 31, 2008
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
View 9 Replies
View Related
May 2, 2013
I have two Tables, Table1 and Table2, in a single Excel spreadsheet. Table1 is our master log, and Table2 contains only Provider Names, Contact Names, Phone Numbers, and Email Addresses. I need to compare column K from Table1 with column AT from Table2. Whenever Excel finds a perfect match, I need Excel to copy AW:AY to AB:AD. For each value in column AT, there will be several identical matches in column K.
I have tried to implement vlookup() and index(match()), but cannot figure either one of them.
Table1 is almost 1500 rows long, while Table2 is not quite 80.
View 3 Replies
View Related
Feb 14, 2009
I am trying to find a formula that would pick out a row from a matrix based on the values in the first column and then return to me each of the subsequent values from that row. I can't do a vlookup because there are a lot of blank cells and which columns have data changes. I've attached an example. The text in A1 will change and cells A3:B5 will show the corresponding values from that row along with the column title. I would prefer a formula to type in and leave in cells A3:B5 but if that can't be done VBA is okay too.
View 6 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
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
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
Nov 28, 2006
I 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.
View 8 Replies
View Related
Dec 21, 2006
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 Related
May 17, 2013
I have attached a demo spreadsheet to define the problem.
I wish to populate column K (Risk Rating) with data retrieved from the table, based on the corresponding information from Columns I & J.
e.g. I5 + J5 = Short term illness or injury + Unlikely. This corresponds to cell E9 (11) in the table so required response in K5 would be 11.
Help Book1.xlsx
View 1 Replies
View Related
Apr 25, 2014
My aim is to return a value from a table (Price of models) using a two way lookup method.
E.g. The code is CA45-AI, I'm looking for a formula to match the first 2 letters 'CA' to the TYPE RANGE (cell range) so excel knows to return a value from the CA row.. then, match the number '45' (from the CA45-AI (cell b3), so in the end.. excel returns the value (price) '90'.
Another example, Cell B5 is RA34-AI, so excel should return a value of 80 from the Price Of Models (cell range).
View 6 Replies
View Related
