Index/match help

S

shav

Hi all,
going by the example for looking up in a range that isn'
sorted in excel help, I get the following examle

=INDEX(A2:B5,MATCH("Pears",A2:A5,0),2)

It's says that the match statement returns the row id. Is it possibl
to replace the column id in the above example so that I can use a matc
statement to return a column id as well. I have a table that has severa
possible values in different columns. Cheers
Sha
 
M

Myrna Larson

Can you give an example? Do you perhaps want to search for the correct column
using another MATCH formula on the first row (the headers)?

=INDEX(A2:K5,MATCH("Pears",A2:A5,0),MATCH("Sales",B1:K1,0))
 
S

shav

That's exactly what I'm trying to do but when the value of the column
reference goes above 3 I get an error even though there's values in
coulmns 3 and columns 4. The formula works when the column value is 2.
very strange!! here's what I'm using

=INDEX([temp.xls]Sheet1!$A$2:$B$8,MATCH(Sheet1!$B$4,[temp.xls]Sheet1!$A$2:$A$8,0),MATCH(Sheet1!$C$4,[temp.xls]Sheet1!$A$1:$E$1,0))

Thanks
 
P

Peter Atherton

The following will search against Row and Column headers
to get numbers for each to put into an address function
and call up the value using the indirect function.

=INDIRECT((ADDRESS(MATCH($B$7,$A$1:$A$4,0),MATCH
($B$6,$A$1:$G$1,1))))

watch the wrapping!

Peter Atherton
[email protected]
 
S

shav

It's ok folks, found th eproblem. I was referring to a cell that wa
outside the range I was specifying. Stupid! Thanks for the help
Sha
 
Top