Hi,

You may mail me the file at ask(at)ashishmathur(dot)com

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"zx6roo" wrote in message
...
Hi, no that didn't work.

The cells on sheet two B3:H7 are all text except column 5 which is a
number.
Sheet one F7 will be the text from anywhere within the sheet two B3:H7area
(except column 5). Each row within the table area has a specific number -
so
if the text matched B3 the number is 1, if the text matched H7 the number
is
1, if the text matched B4 the number is 2.

I want it to automatically match the text in the table then look at the
number in column 5 from the row (which I gave the range names to) and show
me
that number in the cell on sheet one.

Did I explain that cleary? I can attach an example if needed.



"Ashish Mathur" wrote:

Hi,

You could try this

=INDEX(Sheet2!$D$3:$H$7,SUMPRODUCT((B4:C8=F7)*ROW( B4:B8))-ROW($B$3),5)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"zx6roo" wrote in message
...
I have the following individual functions
=INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5)
=INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5)
=INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5)
=INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5)
=INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5)


I need to combine them all in the same cell so IF F7 matches data in
ranges
ONE, TWO, ETC it will return whatever is in column 5.

Ive done some searching on the net and also looked at Microsofts help
on
this. I can see how to create a multiple function if I want it to be
ONE
&
TWO but not ONE or TWO. Ive tried various combinations of functions
but
a
little stuck.

Any help is much appreciated.