Lookup based on multiple variables

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

I've set up a table with variables in A column and row 1.
I would like Excel to find a result based on the
interesection of information in both A column and row 1.
A column has test grades, row 1 has ages. When a test
score is found in A column then look across row 1 until
you get to the age and find the information at that
intersection. Can this be done in Excel?

Thanks.

Diane
 
Hi
try something like
=INDEX(A1:F100,MATCH("grade1",A1:A100,0),MATCH(age_value,A1:F1,0))
 
Frank,

Thank you for your reply. I've been trying for days to
get the formula to work but I'm still having trouble.
Here's what I have that results in a N/A:

=INDEX(SASVerbal,MATCH(C2,'USS to SAS Verbal'!
A1:A62,0),MATCH(B2,'USS to SAS Verbal'!A1:O1,0))

*In this formula SASVerbal is the name of the table where
the data is to be looked up from
*C2 is the cell that has the raw test score
*'USS to SAS Verbal' is the sheet where the table
resides, cells A1:A62 is where the raw scores are listed
*B2 is the cell that has the student's age
*'USS to SAS Verbal' is the sheet (again) and A1:O1 is
the row that lists the ages

So sheet 'USS TO SAS Verbal' has a table named SASVerbal
with 15 columns and 62 rows and what I need is the
intersection of this table where the raw test score
appears in A column and the age appears in row 1.

Thanks for looking at this.

Diane
 
Hi Diane
formula look o.k. for me. Have you checked what both C2 and B2 appear
EXACTLY in this table (check for spaces, formating error, etc). You may
check it manualy with a formula like
=B2='USS to SAS Verbal'!C1
(if you expect to match C1 of your lookup table with B2). This should
return TRUE

If you still have problems you may email me your file and I'll take a
look at it (frank[dot]kabel[at]freenet[dot]de)
 
Back
Top