Index or offset maybe?

[shadow12345]

I have spread sheet with a match that allows me to find row number of the item im looking for in another spread sheet.

My question can i use the row number to select information from the second spread sheet?

The information I want will always be 1 row down from the row number and two columns right.

Example
I have the result row 192

I want to target cell in the second sheet (called stats) b192 and take the information from D193.

Any ideas or suggestions?

 

[Sal Paradise]

Perhaps this?

=INDEX([stats.xls]Sheet1!$D:$D,MATCH("*" & AB25 & "*",[stats.xls]Sheet1!$B:$B,0),"")+1)

 

[Aladin Akyurek]

file is called stats (my fault i was trying something else)

AB25 is a persons name,

Eg: Shadow Excel

the problem comes because in the second sheet (stats.xls) the persons name is combined in a cell like this in coloumn B

Agent Name & ID: Shadow Excel - 2111

So i used wild cards to match the name to stats.xls


We are trying to match ab25 against B in stats and return the value from D (stats.xls) but 1 row below the match result.

That is what the 2nd formula implies:

=IF(AB25<>"",INDEX(stats.xls]Sheet1!D:D,MATCH("*"&AB25&"*",[stats.xls]Sheet1!B:B,0)+1),"")

with a small change AB25<>"" instead of AB25>1.

This formula allows you to work also when stats.xls is not open, while formulas with OFFSET and INDIRECT wouldn't.