LOOKUP With AND Requirements (lookup A Particular Student's Grade In A Particular Assignment)
Jan 26, 2010
I'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value".
My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.
View 4 Replies
ADVERTISEMENT
Jan 14, 2007
I would like to have a student's current overall grade be automatically calculated and displayed as I enter the test scores and extra credit points over the semester, first in percent and then in letter grade.
I have it set up so that the percent is calculated based on the total number of points likely to be obtainable at the end of the semester. But if they have only taken two tests and one extra credit assignment, it takes the total of those and divides it by the semester's total possible, instead of just the two tests and extra credit total possible. (ps: I do not know how many extra credit columns or number of possible points I will have this early in the semester)
Also, how then do I set up a formula to translate that percentage into a letter grade based on A=90-100, B=80-89, etc.?
View 7 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
Mar 26, 2008
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies
View Related
Apr 27, 2009
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
View 3 Replies
View Related
Jan 2, 2009
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
View 7 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
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
Nov 28, 2006
here is an example....
(this is on a sheet called Summary)
----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649
Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....
---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649
The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).
View 3 Replies
View Related
Jun 12, 2007
I have a very large spreadsheet of customer information(I call it the master spreadsheet). Each row contains only 3 things: Account number, product bought, Price
Later I receive the money from the customer for that product(the pay sheet) that contains the exact same thing in the same order: Account number, Product bought, Price paid.
What I'm trying to do is compare the two spreadsheets so that when i receive the pay sheet of cusomters who have paid with the amount it will deduct it from the master sheet.
So it should compare account numbers when it finds a match then it should subtract the amount paid (column C) from the master spreadheet price column(column C also).
sometimes customers don't pay the right price so it has to be a subtraction so I can see if it was over paied, underpaid etc.
Right now I'm still doing it manually combining the two documents sorting it by account number and checking for matches in column A (account number).
View 5 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
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
Feb 21, 2009
Assuming 1st row is a header row
Sheet1, Column A
1230000_XL07 - WB OPS
1230001_XL08 - WB OPS
1230002_XL09 - WB OPS
Sheet 2, Column A
1230000
How do I lookup 1230000 and return 1230000_XL -07 WB OPS in B2
View 2 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
Jan 28, 2009
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies
View Related
Jul 10, 2013
I am compiling a master spreadsheet to analyse progress of students. I have their potential grades in one column and their latest progress grade in another.
I would like an automatic look up of the potential to the progress grade so that if their current grade is BELOW their potential that gets highlighted one colour, if it is above, another colour.
One issue is that the grades are all letters (S, A, B, C, etc.) and that some potential grades are 'dual' (i.e. A/B, S/A) which complicates the issue somewhat.
View 7 Replies
View Related
Jun 13, 2006
my Excel teacher doesn't even know how to do this! The chapter is Working with Multiple Worksheets and Workbooks, and we went into linking workbooks, linking worksheets, using the VLOOKUP function, and saving workbooks as templates. This is Case Problem 2 and i'll attaching the file.
It says:
1. On the Grading Criteria worksheet, enter the text "Exam Average" in cell A1
and the text "Grade" in cell B1.
2. In the range A2:B6, create a lookup table for the range of grades specified by Professor Templeton. (Hint: Each letter grade should be matched up with the lowest score possible for that grade.)
Here are the specified grades:
0 to 49 = F
50 to 59 = D
60 to 74 = C
75 to 89 = B
90 to 100 = A
3. Insert a formula in Column E of each worksheet to calculate the overall score of each student based on the grade table you entered in the Grading Criteria worksheet.
View 9 Replies
View Related
Mar 30, 2008
I have a range of cells C11:C29,G12:G18,G20:G23,G25:G26,G28:G29,C33:C42,G33:G42,C46:C47,G46:G47,C51:C54,G51:G59,C58:C59 that contains letter grades (egs. A+,C- etc). I need the code for a macro that checks each cell from this range and if the value of the cell is A+ then the cell's value changes to 100, if the cell's value is A then change the cell's value to 98, if cell's value is B+ then change to 88 ...and so on...you get the idea (i will complete the rest of conditions but i just need the basic code).
View 6 Replies
View Related
Jan 2, 2010
"If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value."
"If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value."
how can i get the result to be "You have type the wrong data" if i've type that doesnt match the list of the lookup value ?
View 7 Replies
View Related
Nov 22, 2007
I am using an array formula (on the RIM Detail Sheet) to assign a weighting to a value taken from Irregular securities sheet; though the values that this formula need to return don't seem to be correct.
I am specifically referring to row 111 of RIM detail sheet. As the corresponding value on the Irregular securities sheet is > 50%; the array formula should return a result of 2 but instead its returning 10.
View 8 Replies
View Related
Mar 27, 2009
I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.
Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?
Or in the attached file, I would like to say A and 15 and get A15.
View 3 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
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
Aug 17, 2014
I want to find the first topper student in each subject but the formula is not giving correct output.
View 14 Replies
View Related
Nov 5, 2013
I have 3 columns in an excel sheet (ID, Signed by & To be checked), and in another sheet, I have names under a column "Checkers".
I need a macro where in if I run it "To be checked" column in sheet 1 should be equally assigned to the "Checkers" in sheet 2 and the rest should be blanks.
find the below sample:
Before Macro:
ID
Signed by
To be Checked
121
AAA
123
BBB
[code]....
View 9 Replies
View Related
Feb 24, 2014
I need to assign van seating assignments to a client room census. I have a workbook attached. I am hoping a formula can get me there. The only variables are if the cell in column E has TO BE CLEANED or READY in it, then I want the formula to overlook that particular cell. I don't want to assign a seat to an empty room! My workbook should make what I am attempting to accomplish much more clear.
Also, there are either 12 or 15 seats available in each van, but the workbook reflects that.
View 9 Replies
View Related
Oct 22, 2013
I have a grading system for university students, my spreadsheet looks like this:
Student Name Academic Calendar GPA Fall GPA Winter GPA Spring GPA Summer
John Quarter 3.00 2.56 2.89 3.6
Jane Semester 3.1 n/a 3.21 3.7
I think I will need to do some conditional formatting to make it easy to fill the student GPAs .
I was thinking how to enter a formula to have the GPA winter appear as shaded when I enter "semester" in the Academic Calendar column.
View 9 Replies
View Related
May 2, 2008
I having trouble with an intermediate step filling of an array based on the input range data and polynomial degree. The values calculated (xsum and xysum) for the array (F in the code) are accurate, but the function returns #VALUE for all elements.
Function poly_fit(ByVal Xdata As Range, Ydata As Range) As Variant
Dim a As Integer, b As Integer, c As Integer
Dim deg As Integer, num_pt As Integer
Dim Xs() As Variant, Ys As Variant
Dim F() As Variant, XY() As Variant, coef() As Variant
Dim xsum As Variant, xysum As Variant
Xs = WorksheetFunction.Transpose(Xdata)
Ys = WorksheetFunction.Transpose(Ydata)
If UBound(Xs) UBound(Ys) Then
MsgBox "You don't have the same number of X's and Y's"
End
View 9 Replies
View Related
Feb 27, 2009
On Sheet1 I have a small list of names in column A and a number next to the name in column B. This number may vary.
What I want to do is on Sheet2 in column A repeat the name based on the number from sheet1 in column B.
Now the sheet is shared among all of us and macro are out. Is this something that is possible with a function?
View 9 Replies
View Related
Jan 28, 2008
i was asked to make a fair rota and i found this 1 on this website its useful but i need to add to it. if you download this work rota you will understand what i mean
http://www.ozgrid.com/forum/attachme...0&d=1154640274. i need to find out what the formula/macro is used in this work rota ...its exactly what i need but i need to add saturday and sunday to this rota and more duty columns as their is more employees and when i click the button it randomply places employees in different positions.
View 2 Replies
View Related
