Building A Lookup Table With 3 Variables/3 Answers
I'm trying to figure out how best to build a table to do:
IF A1="yes", "no", "na" and
IF B1="yes", "no","na" and
IF C1="yes","no","na" then
"AT" or "IC" or "VE"
So, I need a table that can look up responses to three questions, then lookup the corresponding answer (AT, IC or VE). Some of the combinations of anwers will yield the same anwer (i.e: yes, yes, yes=AT and yes,yes,no=also AT). I'm trying to come up with the most probable answer by evaluating these three questions; Thus; 10 combinations will yield an answer of "AT", 10 combo will yield "IC" and 7 combo will yield "VE". I've done this with 2 variables, but can't seem to get it to work with three...
View Complete Thread with Replies
Related Forum Messages:
Building A Lookup
I need to build a formula that looks up a vertical value and a horizontal value and retrieves the value where those two values meet
Grade ST LOUIS MILWAUKEE SOUTH DAKOTA
C10 30,000 35000 45000
C11 27500 25000 30000
C12 22000 60000 23500
I need the formula to retrieve that value for grade C11 and in milwaukee which is 25,000
Lookup Function To Lookup For Data In Another Table
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.
Locate Value From Table, From Two Predefined Variables?
Struggling a bit on this one! I have tried the index function, but doesn’t quite work how I need it. Consider the attachment as an example:
I have three cells, 2 of which are drop down lists, and the third is simply the result from the input variables. The yellow and green section contains the info for my drop down lists, and the orange contains the data range. Trouble is ‘Index’ is looking at the contents of my cells, and not the corresponding cell address. So lets say I have cells F5 and A8, the answer I would be looking for is 23.7, but because the contents of my input cells are 185, and 5, its looking for column 185 and row 5, when I want it to look for the column/row that relates to these values, i.e. column 6, row 8.
Solution/altenative To Data Table Using >2 Variables
Is there a way to test out a formula using Excel that has more than 2 variables? (If only 2 variables, I would use a simple data table). The formula I am testing has 50X50X20 variables (and as a subset, each variable has 2500 lines of assumptions!). I don't need to see all the results, only the result that produces the highest result. For simplicity (if someone has an answer) let's assume 3 variables are A1, A2 and A3. Formula is in A4 and formula is A1+A2+A3. Assuming A1 can be 1 to 5 and A2 same , A3 same, the long hand calc is for me to run a data table using variables in A1 and A2 the fixing A3 (as 1)..note maximum result....then run data table again using variables in A1 and A2 and changing fixed value in A3 to the number 2...and so on and so on....in this simple example I know the max result would be 15, but much more difficult in my spreadsheet test.
Create Table Of Dimensions With Multiples Of Variables
I am trying to create a spreadsheet containing a table of dimensions that are multiples of up to six loadable variables. I work for a stainless steel tank manufacturer. The body of tanks are formed from coil stock of varying widths. Typically these might be 1200mm, 1500mm, 2000mm and possibly 1800mm as well as the imperial equivalents. This could mean up to eight variables but in reality all are unlikely at any one given time.
We are told the volume that the tank is required to contain and a target preferred diameter. The theoretical height is therefore derived from this information. But now comes the tricky bit. It is desirable, to minimise cost of welding by minimising the number of strakes or wrappers. Can anyone give me a formula or whatever that can be used to do this and hopefully generate a table of close heights up to say 40 meters. Thus allowing an operator to select from say five presented options.
I have a table in a spreadsheet that the three principal widths of 1200mm 1500mm and 2000mm generate. It was created for me a while ago by a gentleman working at the Amsterdam Museum that I’m happy to send for perusal. The trouble is he never told me how he did it and no matter how hard I try, I cannot discover the method or formula. The first column gives the total combined height, the second the number of 1200mm wrappers, the third the number of 1500mm and the last the number of 2000mm. It is that simple
Code To Generate Data Table Based On Variables
I was trying to use the VBA code to generate a data table:
Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer)
Dim initialCell As Range 'specify the upper left cell
Dim RefCell As Range 'the reference cell on the caculator sheet
'activate source sheet
Set RefCell = ActiveSheet.Cells(2, 3)
Set initialCell = ActiveSheet.Cells(initialRow, 1)
'the calculation should be automatic, if not, then calculate
and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
Lookup Table That Will Lookup The Tuition
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.
Copy Input Data To Output Table Based On Variables
For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)
I plan to use command button " CommandButton1" on input sheet to start the calculations
Here's what I want to do after pressing the " CommandButton1" :
1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.
2.for empty cell in the input table paste 0 (zero)
3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output
4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1
5.Do this for all cases (in actual workbook there will be more than 100 cases)
Building A Chart
I am building a chart from a pivot table and the data changes weekly therefore my chart changes weekly and I want to have the same colors on my graphs from week to week. I created this code to keep everything the same. Problem is when some of the data is not there for a given week (say no calls from a certain city) a corresponding data set doesn’t get created and the macro won’t work. Is there a way to run if the a data set is not available and to keep the colors the same?
.Weight = xlThin
.LineStyle = xlAutomatic
Building The Name Of A Variable
I need to update a Variable I called "Croy70" by making up its name from two cells
Left(Range("L" & i), 4) & Right(Range("J" & i), 2)) = i
Where i=2 and L2 is "Croydon" and J2 is "ABC70"
I apologise for this simple question, but I appear to have left my brain at home this morning and all the VBA help files are missing from the computer I'm using!
I can start building.
1. User will first input Medication type in first column. Second Column - Dosage Third Column - Start Date, Fourth Column - End date
There will be multiple entries for the same medication because of dosage change. I can simply get them to insert a new row underneath each medication, and they can then input new dosage, new start/end dates
There will be a cell entry for the form start date, as it will be populated on the main page.
The solution I believe will involve an if statement, with argument being if date is still within range of start/end date, that it will generate text on particular date.
Building A Dynamic Database
I have a workbook with 3 worksheets titled “Numbers”, “Letter” and “NumLtr DB”.
The “Numbers” worksheet is a dynamic listing of numbers in Column A, which also is a Dynamic Named Range using the formula: OFFSET(' Num'!$A$1,0,0, COUNTA('Num'!$A:$A),1)
The “Letters” worksheet is a dynamic listing of letters in Column A which also is a Dynamic Named Range using the formula: OFFSET('Ltr'!$A$1,0,0,COUNTA('Ltr'!$A:$A),1)
I need a macro to duplicate in column A, on the worksheet titled “NumLtr DB”, the first number down as many rows as are needed to equal the full count of letters, and then, in column B, list each letter in succession. This process needs to be repeated once for succeeding each number and then stop.
Start Day Building Out 365 Days
I have Sheet 1 with a series of 10 lists. Sheet 2 will display the information on Sheet 1 based on the day. For example, the user will input a start date. Excel should display item 1 in lists 1-10 on day 1 in the respective column. Then item 2 in lists 1-10 on day 2, etc. When it reaches the end of a list it should start at the beginning. Day 1 shold be the start date. Day 2 shold be the next day, etc. I have lists 1-10 all ready to go, but I am having trouble figuring out how to go about populating the other columns.
Building A Cash Register
I want to build a cash register from excel.
I want to put many buttons, but lets say one on A1 that says $2 dollar item and name and lets say this $2 doillar item button is pushed(clicked on), it will add $2 to lets say the total say A15.
Now I can't even get a Macro to add $2 dollars to A15(or any macros to run at all, anybody have code I can copy, what I understand is you create macro name(give it letter, so yo can press letter and Ctrl to start) then start recording, then you do key strokes or mouse? Then press stop recording and you have macro?).
To get buttons to be viewed I have to go to Tools - Macros - script editor, and drag and drop(button on coding) or double click on button tool bar then File - View in Browser - . I get button but can't get it to do anything.
How do you put say A1 in code to A1=A1+5 or make a memory location out of A1. Now you can go to(while A1 is selected cell) "Insert" then "Name" then "Define" and give A1 a "Name". I can't get this name to work in coding(but I can get the name to work in cells like "Name" +5, will add cell 1 and 5, if you do it in different cell than A1).
Building A Database For Dogs
We have a number of dogs and information about their sire (male parent), dame (female parent) - and those dogs parents.
In essence something like this:
DOG1: DOGDAD, DOGMOM, DOGDAD's DAD, DOGDAD's MOM, DOGMOM's DAD, DOGMOM's MOM
So...I'm thinking 7 fields for each dog.
I started out with an Excel s/ sheet that looked like this:
Dog Sire Dame SireD SireM DameD DameM
DOG1 DOG2 DOG3 DOG4 DOG5 DOG6 DOG7
DOG8 DOG9 DOG10 DOG11 DOG12 DOG13 DOG14
DOG2 DOG15 DOG16 DOG17 DOG18 DOG19 DOG14
Now here comes the issue....what we are trying to do is to find and exclude any dog (in the first 'Dog' column that share ANY grandparent (SireD, SireM, DameD or DameM) with any other dog from the first column.
(As an example above - DOG8 and DOG2 share the same grandparent (DOG14) and needs to be excluded from the final list).
Different Lists For Different Answers
Cell C7 is a drop down list with 3 possibles (Consumer,Commercial, Public). Depending on which is chosen, I want Cell C10 to provide a different list of possibles (Consumer Products, Commercial Products, Public Products).
I have tried to create a custom IF statement with the list choice & sometimes it refuses & sometimes it gives me the list, but with the formula string as the drop down options.
Is this forumula possible or is it beyond excel's capability?
If- Returning 2 Answers
I have a sheet with this:
cell a1 = numbers from 1 to 10 or nothing
cell b1 = TO or OFF depending on cell a1
cell c1 = numbers from 1 to 10 or nothing
Now I have a formula in cell b1 that detects if theres text in cell a1.
IF A1 = nothing, then b1 = off, else b1 = to.
What I want to achieve is to do 2 things on the IF.
If a1 = nothing, then c1 = nothing and b1 = off. else, b1 = to.
how can I do this?
I looked everywhere and no one speaks of double answers on IF.
MAX Multiple Answers
here's the formula that I'm using: =INDEX($C$35:$AF$35,MATCH(MAX(C41:AF41),C41:AF41,0 )) where $C$35:$AF$35 contains names of people & C41:AF41 contains #'s. This works great if there isn't a tie. Is there a formula to search the range, find the max # and if there are two answers display both names. ex. the max# is 3 and both joe and sam have 3. it would then show joe / sam or something like that. Is this possible? If not, then if there is a tie for the max #, to have the cell just display tie.
IF OR Formulas With 7 Answers
I am trying to achieve the following in one formula.
IF an amount is greater than 0 and less than 500,000 put in 490
IF an amount is greater than 500,000 and less than 1,000,000 put in 1000
IF an amount is greater than 1,000,000 and less than 4,000,000 put in 2205
IF an amount is greater than 4,000,000 and less than 9,000,000 put in 5000
IF an amount is greater than 9,000,000 and less than 10,000,000 put in 6500
IF an amount is greater than 10,000,000 and less than 15,000,000 put in 7500
IF an amount is greater than 15,000,000 put in 9500
Macro: Building File List
I want to build a file(name) list of all files in a directory including the ones in all underlying subfolders on any level.
E.g. make at total list of all possible files (not folder-names) in the given directory.
I have a clue, but I just manage to list the files in the first subfolder level - not proceed "deeper" in the folder tree.
Mutilpe Answers To Cell
How would I write out a formula if I currently have this formula/ =COUNTIF(D6:Q6,"a")*8 on cell S6 but I also want to include/ =COUNTIF(D6:Q6,"a-1")*1 and =COUNTIF(D6:Q6,"a-2")*2 and so on till a-7 and i want all this to read on the same cell.
Search And Compile Answers
I have a workbook filled with many tabs / sheets. Each sheet contains the same structure of information and isn't an important aspect of this question.
I've looked at this topic http://www.mrexcel.com/board2/viewtopic.php?t=16683 and I'm unable to follow it well.
Is there a way to search all the tabs for a word and if it matches to copy the entire row into the search tab / sheet? Specifically for each and every searched match.
It would save my users trying to search each tab individually and possibly miss the data they are actually looking for. Hundreds of people use this reference workbook which stores where all our documentation is stored across numerous servers. Each row is one document and its corresponding storage information.
Multiple Answers And Message Box
I have a message box in my spreadsheet. I want it so if the user puts in multiple answers that certain message box appears, if they put in a different set of multiple answers it shows up. For example, the code below is what I am using now. It says that if the user types 5225 in C16 and 3000 in C17 then a specific msg box appears. I want it to say if the user types in 5225 or 5226 or 5227 the same msg box appears but if they type in 5665 a different msg box appears. How would I change this code? ...
Vlookup And Multiple Answers
I have a table whereby I Vlookup a different spreadsheet on an order number (in this instance Z011352/001). There are multiple sketch numbers associated to this Z number.
I would like to be able to input the Z number and then obviously the Vlookup pulls up the first sketch number. This works fine.
If I then input the same Z number again it pulls up the same sketch number, but I want it to look in the current table (the one i'm entering the Z number onto), see if the sketch number is already present and if it is pull up the next sketch number in the list on the separeate spreadhseet. (I hope this is clear enough)
My Vlookup code is:
=IF(C1070"",VLOOKUP(C1070,'[CrossingsSchedule.xls]Update Worksheet'!$E$1:$AC$10000,20,FALSE)," ")
If Statement With 4 Diffent Answers
This is my current formula that im using in cell U6
=IF(L6>=500,"Sept 8 2009","Sept 1 2009")
it works fine with just 2 answer cuz if its not one answer is another
=IF(AND($B$2>=0,$B$2>=500),"Sept 8 2009","Sept 1 2009")
found this one online but it got frustrating...i change the values to mine
so what im looking for is a if formula or similar to the following
if L6 is between 0-500 i want U6 to say Sept 1 2009
if L6 is between 501-750 i want U6 to say Sept 8 2009
if L6 is between 751-999 i want U6 to say Sept 15 2009
if L6 is between 1000-1200 i want U6 to say Sept 22 2009
i try both if formulas (stated above) both but both return one or more results...i just want one
0.00 than i would get the result Sept 1 2009
253.52 than i would get the result Sept 1 2009
647.36 than i would get the result Sept 8 2009
750.01 than i would get the result Sept 15 2009
1100.00 than i would get the result Sept 22 2009
if above 1200 i would get the result sorry your amount owed is to high
formulas are preferred *but will work with macros and other options
*if using macros or vba or what have you please explain how it will work
Building Musical Scales - (circular List)
I want that Excel writes in a row the a sequence of musical notes corresponding to a certain tonality;
I have in one cell the musical notes, which are validated data that comes from a list and I could pick one from this list:
C C# D D# E F F# G G# A A# B
chosen one of the notes, excel would write down in a row the corresponding chromatic scale for that note. example - choosen A:
A A# B C D D# E F F# G G#
As you see, the scale begins with A, and then when it gets to the end of the list (tone B) it goes back to the beginning (C). In other words, what I want to implement it´s sort of a circular validated data.
Building A Formula To Increase Price List 8%
I've built a spreadsheet that accurately displays my company's price list. However, from time to time, there are increases and/decreases, by percentages. I would like to know how I can build a formula that would allow me to quickly update the pricesheets by the appropriate percentage, without having to manually do so, one cell at a time.
Method To Show Multiple Answers
I am currently maintaining a database that keeps up to date records of employees in my company and their vacations including their nationality dept. etc. for the vacation reports i have a "last day of work," "return to work date," "Actual Return to work date," and at the end a "remarks" column,
Moreover I need to report how many employees per department/Discipline are on leave ex. ( mechanical, electrical, and so on.) That I did using countifs having whoever is remarked as "na" vs. actual return date, Discipline vs. each discipline. All works fine but what i want to ask is there anyway that i can list the names of employees that are on vacation under each discipline?? Ie. if 3 are in the electrical engineering department, can i list their names? or if Today()>Actual Return to Work day (ie they are late and have not arrived yet) is there a way that it can list the names of multiple employees? rather than having to work against each name etc.
Calculating A Number From Answers To An Audit
I have created a workbook that I store data from my audits, this data is in the form of Y if compliant, N if noncompliant and N/A if not applicable. Where the fun part begins is that each question has a different risk involved. I have used a simple 1 to 5 risk scores and given scores for compliance and non compliance to each score, for example a risk 1 if compliant is 100 points, if non compliant is -100 points, all the N/As are worth 0.
I currently calculate the totals in a different sheet in the work book, but I do this kind of manually, I have calculations to work out the totals and percentages and all that, but I cannot figure out how to get the Ys and Ns to appear in this sheet as 100 or -100. All I do at the moment is bring the Y, N or N/A over with a simple =corresponding cell in sheet 1 then manually change this to the number I require.
'3-way' Table Lookup
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.
Lookup For A Table
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.....
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
Lookup In A Table
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'??
Check Multiple Choice Quiz Answers
I have a table in excel that i need to use to mark a 20 question quiz. I have the correct answers in one column and the students answers in the next column. I want to mark a correct answer with 1 and an incorrect answer with 0 marks. I know how to use IF, however the students answers can vary eg, correct answer could be B and D but the student writes B + D. Is there any way of marking this correct even tho it is not exactly written out correct?
Lookup Table With Exceptions?
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.
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 Data From Table
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
Lookup In Table With Duplicates
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?
Lookup Transposed Table
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?)
into this table...
- If I need to attach my working file.
Lookup When Value Not In 1st Column Of Table
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?
Use Lookups To Change A Value In The Lookup Table
I have a table which contains information about stock, item description, price, barcode, stock qty etc.
I have used lookups to make an invoice so I can sell the product and print a receipt etc. I want to be able to take away the amount sold off the specific product that was found using the barcode and a lookup and take that amount from the stock qty column in the lookup table.
Is there a way in which this can be done? If so how?
I have included an example of the invoice and the lookup table that I want the stock to be taken from.
Table Array - Data Lookup
I have a spreadsheet that I would look up two criteria in the table array, which first is down the column and next cross the row.
It would be easier to explain the scenarios by referring to the attached file.
Double Lookup From Two Dimensional Table
I 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.
Output Based On Lookup Table
I created a Vlookup table so that when you type a ticker into Cell B3, it tells the user how many points and what percentage of the criteria the stock passes. I would like to create a macro (or something) that takes each ticker in the Ticker sheet, enters it in the Cell B3, and then transfers the data that the vlookup table produces to the output sheet. My attachment is a simplified version of the real spreadsheet.
I hope this isn't a duplicate question. I did look, but maybe I'm not phrasing it correctly. A reference to a similar problem works too.