How to lookup data in a row and column

[Guest]

I have a chart in excel that shows salaries based on two criteria. You go
down the column to find the first criteria, then across that row for the
second (much like a mileage chart). How can I get excel to lookup this
information? I only know how to use lookup to find data based on one cell
location.

 

[Don Guillett]

a combination of match, match and index?

 

[Dave O]

You can do this by nesting a MATCH() function inside a VLOOKUP()
function. Start the VLOOKUP using the the first criterion, then
instead of entering a column number in the VLOOKUP use the MATCH()
function to return a column number. However, since your first
criterion occupies column 1 of the array, your MATCH function needs to
be incremented by 1 to adjust for it.

 

[Alan Beban]

I have a chart in excel that shows salaries based on two criteria. You go
down the column to find the first criteria, then across that row for the
second (much like a mileage chart). How can I get excel to lookup this
information? I only know how to use lookup to find data based on one cell
location.

If you use row and column headers (first criteria and second criteria)
that are valid range names, then highlight your data range, including
the headers, click on Insert|Name|Create and check Top row and Left
column, then OK, you can use the "Intersection Operator", i.e., the
space. E.g.:

CriterionC1 CriterionC2 CriterionC3
CriterionR1 $1 $2 $3
CriterionR2 $4 $5 $6
CriterionR3 $7 $8 $9

=CriterionC2 CriterionR3 will return $9
=CriterionR3 CriterionC2 will return $5, etc.

Or, as has been suggested (though I don't understand the comment about
incrementing the MATCH function by 1), with the first criterion in Cell
E1 and the second in Cell F1

=VLOOKUP(E1,A14,MATCH(F1,A14,0))

Alan Beban

 

[Dave O]

Your kung fu is better: by extending the range of the MATCH function
from column A instead of column B (where I started it) you obviated the
need for the increment by 1.

 

[Alan Beban]

If you use row and column headers (first criteria and second criteria)
that are valid range names, then highlight your data range, including
the headers, click on Insert|Name|Create and check Top row and Left
column, then OK, you can use the "Intersection Operator", i.e., the
space. E.g.:

CriterionC1 CriterionC2 CriterionC3
CriterionR1 $1 $2 $3
CriterionR2 $4 $5 $6
CriterionR3 $7 $8 $9

=CriterionC2 CriterionR3 will return $9
=CriterionR3 CriterionC1 will return $5, etc.

Wow! I don't know how I did that. It should of course be

=CriterionC2 CriterionR3 will return $8
=CriterionR3 CriterionC1 will return $7, etc.

Alan Beban