Complex reference question

[Bunny_British]

Hi!

I would love to know if anyone knows I would go about doing this. So I have to following:
- MASTER spread sheet with columns of Scrambled number sequences.
- Individual sequence sheets that reference a columns of scrambled numbers BUT start haphazardly anywhere in the sequence.

I want to be able to lookup the column from my individual sequence sheet and the first number in the sequence from that column of the master sheet and then give me the value of the next cell down so that I can drag my formula down and autofill that sequence on the individual sequence sheet.

Have tried a VLookup with Ifs and also an Index and Match but I seem to be missing something as it's telling me I've got too many arguments.

I can provide screenshots of all of this to anyone who needs to see to understand what I'm trying to do.

PLEASE HELP!

 

[FDibbins]

Can you show some sample data, a few expected outcomes, and what you have tried?

Sounds to me like you need something like...
=index(master!data-range, match(cell-with-search-item, master!range-with-all-search-items,0)+1)

 

[FDibbins]

In response to your PM to me, can you copy/paste some sample data here please?

 

[Bunny_British]

Hi,

Without showing you, it's going to be difficult to explain and I can't insert an image here.

Essentially it look like this
- Individual Sheet:

A B C D E
1 Master Column: 23
2
3 Sequence:
4 56
5 =VLOOKUP(E2,Master!A2:E2,INDEX(Master!A2:E2, MATCH(Individual!B4, Master!A4:D7,0)+1))

- Master Sheet:

A B C D
1
2 0 21 23 35
3
4 34 42 56 51
5 43 24 65 15
6 44 22 66 11
7 26 14 32 45

Each column on the Master has an individual sequence that needs finding first but then each Individual sheet starts at a random number of the column which is why I want to be able to lookup E1 on the master and then find B4 in that column and reference the cell below.

Is this possible?

Many thanks!

 

[Bunny_British]

sorry - the spacing went all funny when I posted that.
- Individual Sheet:

_____A___B___C___D___E
1______Master Column:__23
2
3__Sequence:
4________56
5________=VLOOKUP(E2,Master!A2:E2,INDEX(Master!A2:E2, MATCH(Individual!B4, Master!A4:D7,0)+1))

- Master Sheet:

_____A_____B_____C_____D
1
2____0____21____23____35
3
4____34____42____56____51
5____43____24____65____15
6____44____22____66____11
7____26____14____32____45

The issue is that the formula has to lookup b4 as the sequence may start with 66 or 32 or 65, so it's got to be able to look at the next one down in the column after having found the right master column number from e2. Does that make sense?

 

[FormR]

Hi, welcome to the forum

If I'm understanding correctly then maybe you could try this.

=INDEX(Master!A:D,MATCH($B$4,INDEX(Master!A:D,0,MATCH($E$2,Master!A2:D2,0)),0)+1,MATCH($E$2,Master!A2:D2,0))

 

[Bunny_British]

That works amazingly! Thank you so much.