using multiple vlookups confusion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

confused teacher - 11 Oct 2007 23:43 GMT

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 to this Message

Pete_UK - 12 Oct 2007 00:07 GMT

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
teac...@discussions.microsoft.com> wrote:

> My skills are limited.....
>
[quoted text clipped - 6 lines]
>
> help!!

Reply to this Message

confused teacher - 12 Oct 2007 23:27 GMT

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

> 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:
[quoted text clipped - 28 lines]
> >
> > help!!

Reply to this Message

Peo Sjoblom - 12 Oct 2007 23:44 GMT

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

Signature

Regards,

Peo Sjoblom

>I cannot see how this would work!
>
[quoted text clipped - 46 lines]
>> >
>> > help!!

Reply to this Message