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]

=OFFSET(B192,1,2)

So just replace 'B192' with your formula to get B192.

 

[Aladin Akyurek]

Care to post the MATCH formula you have?

 

[shadow12345]

I dont have B192 I have 192 no B

this is what im using to look it up

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

if that helps. I tried

IF(AB25>1,offset(MATCH("*" & AB25 & "*",[stats.xls]Sheet1!$B:$B,0),1,2,"")

and

IF(AB25>1,offset(b & MATCH("*" & AB25 & "*",[stats.xls]Sheet1!$B:$B,0),1,2,"")

But they don't work

 

[Sal Paradise]

Try:

OFFSET(INDIRECT("B"&IF(AB25>1,MATCH("*" & AB25 & "*",[stats.xls]Sheet1!$B:$B,0),"")),1,2)

 

[shadow12345]

I get an error message saying my formula contains an error. It highlights the first *

 

[Aladin Akyurek]

I dont have B192 I have 192 no B

this is what im using to look it up

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

if that helps. I tried

IF(AB25>1,offset(MATCH("*" & AB25 & "*",[stats.xls]Sheet1!$B:$B,0),1,2,"")

and

IF(AB25>1,offset(b & MATCH("*" & AB25 & "*",[stats.xls]Sheet1!$B:$B,0),1,2,"")

But they don't work

Looks like...

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

Why is it not just?...

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

 

[shadow12345]

im not sure i understand that last post. I tried the last formula you posted... no error but i get a 0 as a results.

I checked the formula arguement list and [sym.xls]Sheet1!D:D in the index is returning #num

 

[Aladin Akyurek]

im not sure i understand that last post. I tried the last formula you posted... no error but i get a 0 as a results.

I checked the formula arguement list and [sym.xls]Sheet1!D:D in the index is returning #num

What is not clear? Is the file [stats.xls] or [sym.xls]?

I have the impression that you lookup AB25 in an external file, whose name is either stats.xls or sym.xls.

If AB25 is supposed to be a number, why use wild cards?

Are we not matching AB25 against column B in the external file and returning a corresponding value from column D of the external file?

 

[shadow12345]

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.