Index or offset maybe?

shadow12345

Well-known Member
Joined
May 10, 2004
Messages
1,238
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?
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

shadow12345

Well-known Member
Joined
May 10, 2004
Messages
1,238
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

Well-known Member
Joined
Oct 23, 2006
Messages
2,457

ADVERTISEMENT

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

shadow12345

Well-known Member
Joined
May 10, 2004
Messages
1,238
I get an error message saying my formula contains an error. It highlights the first *
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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...
Code:
=IF(AB25>1,
    INDEX(stats.xls]Sheet1!C:D,
     MATCH("*" & AB25 & "*",[stats.xls]Sheet1!B:B,0)+1,
     2),"")

Why is it not just?...
Code:
=IF(AB25>1,
    INDEX(stats.xls]Sheet1!D:D,
     MATCH("*" & AB25 & "*",[stats.xls]Sheet1!B:B,0)+1),"")
 

shadow12345

Well-known Member
Joined
May 10, 2004
Messages
1,238
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
May 10, 2004
Messages
1,238
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,110
Members
414,289
Latest member
sonintebil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top