Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using multiple vlookups confusion
My skills are limited.....
I am, creating a spreadsheet for a group of children. They can complete 1 of 3 tests according to ability and I want to create a function where if test type is selected (a,b,or c) then a corresponding lookup table can be selected to convert a raw score to a grade. e.g. test a refers to lookup table a, test b to table b etc.... help!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using multiple vlookups confusion
Assume your raw score is in A1, with test type in B1. Then you could
have something like this in C1 to select the appropriate table: =VLOOKUP(A1,INDIRECT("table "&B1),2) This assumes your tables would be like this: 0 E 25 D 40 C 55 B 70 A or something similar, where a score of 25 to 39 would merit a D, for example. Hope this helps. Pete On Oct 11, 11:43 pm, confused teacher <confused wrote: My skills are limited..... I am, creating a spreadsheet for a group of children. They can complete 1 of 3 tests according to ability and I want to create a function where if test type is selected (a,b,or c) then a corresponding lookup table can be selected to convert a raw score to a grade. e.g. test a refers to lookup table a, test b to table b etc.... help!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using multiple vlookups confusion
I cannot see how this would work!
I have 3 lookup tables for the three tests. I cannot see how you can point to a particular table from this command unless you write three options into the c(A1,INDIRECT("Table** part of the function. I cannot do this without returning an error message. Am I just being thick?? (rhetorical!!) Thanks for the help though! J "Pete_UK" wrote: Assume your raw score is in A1, with test type in B1. Then you could have something like this in C1 to select the appropriate table: =VLOOKUP(A1,INDIRECT("table "&B1),2) This assumes your tables would be like this: 0 E 25 D 40 C 55 B 70 A or something similar, where a score of 25 to 39 would merit a D, for example. Hope this helps. Pete On Oct 11, 11:43 pm, confused teacher <confused wrote: My skills are limited..... I am, creating a spreadsheet for a group of children. They can complete 1 of 3 tests according to ability and I want to create a function where if test type is selected (a,b,or c) then a corresponding lookup table can be selected to convert a raw score to a grade. e.g. test a refers to lookup table a, test b to table b etc.... help!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using multiple vlookups confusion
You can use a cell reference where you put the name of the table and then a
dropdown using datavalidation, allow list and in the source box type table,table2,table2 or something more descriptive, then use something like =IF(OR(A1="",E2=""),"",VLOOKUP(A1,INDIRECT(E2),2,0 )) Then when you select the table in the dropdown in E2 it will lookup in the particular table that is selected -- Regards, Peo Sjoblom "confused teacher" wrote in message ... I cannot see how this would work! I have 3 lookup tables for the three tests. I cannot see how you can point to a particular table from this command unless you write three options into the c(A1,INDIRECT("Table** part of the function. I cannot do this without returning an error message. Am I just being thick?? (rhetorical!!) Thanks for the help though! J "Pete_UK" wrote: Assume your raw score is in A1, with test type in B1. Then you could have something like this in C1 to select the appropriate table: =VLOOKUP(A1,INDIRECT("table "&B1),2) This assumes your tables would be like this: 0 E 25 D 40 C 55 B 70 A or something similar, where a score of 25 to 39 would merit a D, for example. Hope this helps. Pete On Oct 11, 11:43 pm, confused teacher <confused wrote: My skills are limited..... I am, creating a spreadsheet for a group of children. They can complete 1 of 3 tests according to ability and I want to create a function where if test type is selected (a,b,or c) then a corresponding lookup table can be selected to convert a raw score to a grade. e.g. test a refers to lookup table a, test b to table b etc.... help!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple VLookups - Can anyone help me please? | Excel Discussion (Misc queries) | |||
Sum a row of multiple vlookups | Excel Worksheet Functions | |||
Multiple Vlookups | Excel Worksheet Functions | |||
multiple vlookups | Excel Worksheet Functions | |||
multiple vlookups | Excel Worksheet Functions |