define match array using 2 worksheets

[oats]

hi, i'm trying to define an array for a match function. the function will search in an adjacent worksheet, but i want the array to be defined by the values in 2 cells in the current worksheet.

say the current worksheet is W1 and the reference worksheet is W2

the array containing the valid info in the W2 is B1:B400

in W1, cells C4 and C5 have values corresponding to specific rows in W2

i only want the match function to search within those rows

i tried something like this (which did not work):

MATCH(G$2,([AXS.xls]Sheet1!$B($C4):[AXS.xls]Sheet1!$B($C5)))

where i clicked on C4 and C5 in W1 for those references

and G2 contains what i'm looking to match


thanks!

[DonkeyOte]

I would opt for INDEX (you will invariably get an INDIRECT suggestion also)

Your references of W1 & W2 don't really tie out to your sample formula but based on that perhaps...

=MATCH(G$2,INDEX([AXS.xls]Sheet1!$B:$B,$C4):INDEX([AXS.xls]Sheet1!$B:$B,$C5))

note you're not presently conducting an Exact match - by omitting the return_type from the match it will default to 1 -- see XL help for more info. if req.

[oats]

sorry, W2 refers to AXS.xls and W1 is the current worksheet