using multiple vlookups confusion

[Guest]

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!!

 

[Pete_UK]

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

 

[Guest]

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

 

[Peo Sjoblom]

You can use a cell reference where you put the name of the table and then a
dropdown using data>validation, 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