Index/match help

[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

 

[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))

 

[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

 

[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
(e-mail address removed)

 

[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