Index Match formula using range name

Hi All

I am trying to tidy up a large workbook, and need some help with use of range names in the Match function.

I have the formula working on the attached sample, but wonder whether it's possible to get the Match function Lookup Array to pick itself from the range name.

Thanks in advance

Jon

You have your formula the wrong way round

=INDEX(Test_1,MATCH($B6,Sheet2!$C$7:$H$7,0),MATCH($C6,Sheet2!$C$7:$C$12,0))

should be

=INDEX(Test_1,MATCH($C6,Sheet2!$C$7:$C$12,0),MATCH($B6,Sheet2!$C$7:$H$7,0))

Thanks Squiggler, I rushed the sample sheet a bit.

What do they say about checking things....

I've attached the corrected sheet, still hoping that there's a way of using the range name to give the match lookup array

Thanks

Jon

You can do this which is what i do for tables

if you need multiple tables you can code

=INDEX(indirect("TableName" & "_Body"),match(ValueB,indirect("TableName" & "_ValueB"),0),match(ValueA,indirect("TableName" & "_ValueA"),0))

(see sample sheet as to how I named It) using names like this gives it more clarity to find your errors as well!)

(note I only used ValueB as a sample rather than $c$1 in the match statement)

I guess what you want to do is make 3 names for each table

MyTable
table

TableValueA
=offset(mytable,0,0,1,columns(Mytable))

TableValueB
=offset(MyTable,0,0,rows(Mytable),1)

then you can do

Index(Mytable,match(ValueB,TablevalueB,0),match(valueA,tablevalueA,0))

If your table changes just change MyTable to reference the new table limits!

If you Want to see the Names

Type MyTableA in the name box and press enter

Thanks again Squiggler, this works a treat.

Either I'm going mad [probable], or I think you may have attached the original spreadsheet.
I have re-attached the file, with the ranges updated as shown in your post, and additions to the original table in case anyone else finds it useful.

Do you know if there is a limit to the number of ranges that can be defined?

Edit - I've also just seen this link on another thread, which could be combined with the solutions here to provide a more automated approach to the whole issue. http://www.cpearson.com/excel/named.htm