# Lookup Values For Score Combination

Feb 21, 2014I have a data array as follows..

Data1.png

I have a requirement as follows..

I need system to fetch values for student/subject score combination, which are in random order.

Req1.png

I have a data array as follows..

Data1.png

I have a requirement as follows..

I need system to fetch values for student/subject score combination, which are in random order.

Req1.png

ADVERTISEMENT

I have a table of data where each row is a person, and each column is a group that person might belong to. In the intersecting cell of a person row and group column is text showing what position they hold in that group (eg Director, Head, member etc). In many cases the cell is empty because the person is not involved with the group.

There are then two lookup tables which have scores; one for the group name and one for the position type. The group name is always the column header. I have used these lookup tables as I would like the scores for each group or position to be easily adjustable.

Probably best that I just attach an example. I tried to solve this using SUMPRODUCT. VLOOKUP on the two lookup tables would have been good but it seems it doesn't work with arrays the way I expected. My attempt (which doesn't work obviously!) is given.

I have a paper-based scoring sheet that we use to determine a rating category. I cannot figure out how to convert this scoring sheet into a lookup region and use some type of LOOKUP formula to determine the rating. I have looked at the various Grading examples on the Internet that show you how to look up a grade, based on a score, but they only look up a grade, based on a single input (the score).

Here I have two values going in--the person's age and their score, and I need to look up the rating, which is the column heading.

Can anyone point me in the right direction?

I've attached a sample spreadsheet which shows the original scoring sheet.

I'm trying to use a combination of Hlookup and COUNTIF. I'm selecting a date value in a cell using data validation. I'm then wanting to write a formula to lookup that value in a row of dates, and then use a countif to find all the '1' values in that column.

View 6 Replies View RelatedI am trying to do a lookup from the price list based on the below data file, my problem is I need the price that falls within the qty range from the price list, the price list is not consistent and changes with every item. I would like one formula that is sophisticated enough to work no matter where the qty range lies.

Item #_____Qty Ordered________Price List

20800______1,500

20800______4,000

20800______6,500

20800______8,000

20800______9,050

20800______11,000

20800______14,000

Price List

Item #____Order Qty________List Price

20800_____2,000___________$3.00

20800_____4,000___________$2.95

20800_____6,000___________$2.90

20800_____8,000___________$2.85

20800_____10,000__________$2.80

20800_____12,000__________$2.75

20800_____14,000__________$2.70

I am having a difficult time with a look up. It would be very hard to explain so I'll attach a copy of the section of the worksheet that the problem lies on with comments so you can see whats going on. The problem there is a numbered list with a reference number i can't seem to figure out a lookup that will look in the chart above and find the row associated to the reference number and according to how many before it have that reference number find a secondary reference number listed in the column above. The attachment should clear it up.

View 4 Replies View RelatedI want to see if i can make a formula that works out how a player got a score based on a scoring system. See the attached file for more details. I want this to show that the player scored or didn't score, played a full match or was a substitute, was booked or wasn't booked and was sent off or wasn't sent off.

View 5 Replies View RelatedI have set of people where by based on Level & designation want to find the percentile on their CTC.

Attached is the sample file. Percentile.xlsx

i've written the code below but somehow the output is not what i want.

i have a multiple records and each records contain 30 rows of info i have 3000 over records and they are all in one sheet. i need to find record using more than 1 criteria. I've written the code below. how to use VBA to search for particular strings in cells?

for e.g.

A1 : hello

A2 : world

A3 : anyhow

i want to find the cell that contains "yh" using VBA, and the output should be in cells A3.....

I need a formula that can provide all possible alphabet combination in another cell .

example

COLUMN A A1 = RED

COLUMN B B1:B50 = BELOW COMBINATION / RESULT

RED

ERD

DRE

EDR

DER

RDE

I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.

I'm struggling to do this in a Macro but I don't get it.

I'm tying myself in knots when trying to combine logical formulas to return a text value based on multiple cell values.

Cell A1 can equal 1-10, and cell B1 can also contain the values 1-10.

I want C1 to display the text "YC" if B1 value is equal to 9 or 10. I want C1 to display the text "SC" if B1 value is 5-8, and A1 value is 4-10. Any other combination of values should return a blank cell.

I tried splitting this into two components before trying to make it fit into a single equation, and i can achieve the first condition of display "YC" =IF(AND(A1>=9,A1

I guess this would work in a similar manner as Solver, but where Solver tweaks cell values to equal a given total, I've got a set of cells and I need to find the combination that equals an amount in another cell. Is there a way to do this?

View 7 Replies View Relatedim 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?

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.

I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.

I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.

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?

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.

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 RelatedI have a little bit of problem with lookup function. When i paste values from another worksheet (paste special, values) in a cell which is lookup value i get #N/A. These values are numbers. When i put '7 for example i get the values i want from lookup table. I have a lot of these cells and its tedious job to put ' in front of every value. Is there a quicker solution?

View 3 Replies View RelatedI tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:

=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)

B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.

is it possible to lookup the last 5 values?

I want to lookup letter "A", from a list of 300 parts. "A" may appear 30 times, but I only want to see the last 5 results (Sorted in chronological order).

If I have 5 cells, and perform a VLOOKUP it will always only return the first occurance of "A". I need to find a way of the second cell "forgetting" the first occurance and moving on to the next one and so on

Can any one tell me out in getting the lookup value.

the file is attached here with.

I have a spreadsheet with two tables. Table1 (Summary) is my summary data, table2 (Data) is the array of data i need to pull from.

I am trying to create a monthly summary report. The values in table1 include values: date, creative element, revenue. I have both date and creative element defined in table-Data, I want the sum of revenue from each month entered into my summary report. My Data table has multiple values for each date/creative element combination.

Ex of my Data table:

Jan 2008 GoogleBase $1000

Feb 2008 Shopzilla $1500

Jan 2008 GoogleBase $1500

So if my data set was above, the summary of data for Jan 2008 for Googlebase would = $2500.

See attached w/Note. I would like to return the number in column B that corresponds to the value in column A that is closest to a specified number (input in cell A2).

View 7 Replies View RelatedA B CD

100 233 a 1

100 454 a 2

100 111 b 3

100 233 b 4

200 345 a 5

200 432 a 6

200 233 b 7

200 333 b 8

300 336 a 9

[code]....

I have values in 4 columns A, B, C & D. I have to get the values from cells in Column D corresponding to maximum & minimum value from column B when the corresponding cells in column A has either "100" or "300" & column C has a value "a". This means, if column A has either "100" or "300" & Column C has "a", then Column B has following corresponding values - 233, 454, 336, 654. Out of this the max. is 654 for which corresponding value in Column D is 10 (First output) & the min. is 233 for which corresponding value in Column D is 1 (Second output). Please note the value 233 & 654 is repeating in "200" & "400" range also.

I have a table that has 1000 people who have a duration of time associated with them. I need to get the names and times of the top 10 people. I would prefer to use formulas so that I can just make a template that I can paste my data into.

View 9 Replies View RelatedI am trying to create a table that references data in a much larger report that constantly has rows and columns added and deleted off of it. Here is an example:

---------------Juice Sell In-----Desert Sell in-----Total Sales

James--------34%---------------8%---------------22

Frank---------22%---------------2%---------------18

This is a simple example of what I would be trying look data up from. But there are hundreds or rows and columns in the actual report, so I am creating another document that just pulls the data I need to review off of the larger report. New rows and columns are added to the report on a daily basis. So I am trying to have a cell return a value as a result of an intersection of 2 other values that remain constant. For example: Looking up the intersection of the row that contains "James" and the column that contains "Total Sales" would return "22" as the result in the cell.

I want to match two different values in the same row and then have it return another value in that row.

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.

Copyrights 2005-15 www.BigResource.com, All rights reserved