# INDEX MATCH when the target column is not known

#### tonkerthomas

##### Board Regular
I've tried to find another thread that deals with this topic, without success, so here goes:

I have a very large block of data: thousands of rows by hundreds of columns. I need to identify a known value in that block and return the value two cells to the right of it. I can use INDEX to ascertain which row of my data the value will be in, but it could be in any column. How do I locate the value, and how do I offset my return?

Example:

<tbody>
</tbody>

I'm looking to find Clive, say, and want to return Bert. I know that Clive is in team B but I don't know where in the columns his name appears so I can't INDEX MATCH with Colhead2. I'm sure there's a way of doing this but I don't know how and an hour of Googling hasn't helped me. So, can you guys?

Jeff

Last edited:

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Fluff

##### MrExcel MVP, Moderator

+Fluff New.xlsm
ABCDEFGHIJK
3TeamBEricCliveAgnesBertDoraRitaCliveBert
4TeamCKatieMaryJakeWillKikiHarry
Lookup
Cell Formulas
RangeFormula
K3K3=INDEX(B2:G4,MATCH(J2,A2:A4,0),MATCH(J3,INDEX(B2:G4,MATCH(J2,A2:A4,0),0),0)+2)

Last edited:

#### tonkerthomas

##### Board Regular
Aha! An INDEX MATCH INDEX MATCH! That's logical, but sadly, too complicated for this bear of very little brain to figure out by himself. Thanks a million, Fluff - your time is very much appreciated.

Jeff

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### donblue

##### New Member
try this =INDEX(\$A:\$A,(ROW()-1)*3+COLUMN(A1)).
I used it to make print out available in 3 columns similar to Words print 'number of columns' excel2013

#### Fluff

##### MrExcel MVP, Moderator
Did you realise that this thread is over a year old?
Also you formula does not do what the OP was asking for.

Replies
22
Views
613
Replies
7
Views
240
Replies
4
Views
383
Replies
3
Views
330
Replies
2
Views
341

1,148,108
Messages
5,744,874
Members
423,907
Latest member
zerocool88

### 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.

### Which adblocker are you using?

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

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