PDA

View Full Version : INDEX MATCH then go to next cell etc



Kekoalani
2017-09-01, 06:48 PM
I am trying to use this formula, =INDEX('Knit Plan'!D: D,MATCH("LON615",'Knit Plan'!C:C,0)+1), and have the +1 change to +2 as I drag the formula down to the other cells. I have Sheet 2 with COLUMN# 3 showing my data type. The data in COLUMN# 4 is what I need to pull. My formula is finding LON615, then copying the data to the cell next to it, 39E04. Well I also need the 4B612 under the first cell and etc. I don't know if there is a formula for this or if there is a way to have the +1 auto add when I drag? Thanks for help in advance.

4
108
LONJS
68308


4
108
LONJS
71G11


4
144
GL544F144TC
7A409


4
144
GL616DF144TC
6C903


4
144
GL616DF144TC
6C904


4
144
LON615
39E04


4
144
LON615
4B612


4
144
LON615
4B613

NBVC
2017-09-01, 08:11 PM
Change the +1 to something like ROWS(A$1:A1)

Kekoalani
2017-09-05, 01:56 PM
Change the +1 to something like ROWS(A$1:A1)

Thanks for the info. The problem is the information is never the in same the location. One group might have 20 rows while the next week might have 10. To use ROWS I would need to know the location of the data correct?

Bob Phillips
2017-09-05, 02:22 PM
How about something like

=INDEX('Knit Plan'!D:D,MATCH(A2,'Knit Plan'!C:C,0)+COUNTIF($A$1:A2,A2)-1)

Kekoalani
2017-09-05, 03:34 PM
Thanks for the replies. I don't think I gave everyone enough info. The TABLE#1 shows what I need in Row 5 copied from the TABLE#2 in row 175. I need the information copied to each row in TABLE#1 from TABLE#2. And when I pull the information next week, row 175 in TABLE#2 could be completely different. Let me know if anyone needs more information. Here is my current formula in TABLE#1 A6=INDEX('TABLE#2'!D:D,MATCH("LON615",'TABLE#2'!C:C,0)+1)
7334

7335

Bob Phillips
2017-09-05, 05:29 PM
Post the workbook, I for one will not gp to the effort of trying to recreate that data.

An dhow do you know that the lookup value is LON615 in Table #1?

Kekoalani
2017-09-05, 07:10 PM
Post the workbook, I for one will not gp to the effort of trying to recreate that data.

An dhow do you know that the lookup value is LON615 in Table #1?

Are you wanting me to post the sheets I am working in?

It is a machine type for each group of data I am looking at.

AliGW
2017-09-05, 09:01 PM
Yes, post the sheets you are working on.

Bob Phillips
2017-09-05, 10:28 PM
It is a machine type for each group of data I am looking at.

That tells me what it is, I asked how you know that is the value to lookup? To automate it, we will need to know what we are looking for, something in the data.

Kekoalani
2017-09-07, 04:14 PM
I had to shrink and delete some sheets to get the file able to upload. Each group has a different machine group and the data is imported from a mainframe. I am trying to copy data from sheet Knit Plan to Commitments according to the machine group.

Kekoalani
2017-09-08, 02:53 PM
Does anyone need a little more explanation or better info?

AliGW
2017-09-10, 07:38 AM
Your attachment is complex - where are we looking? Also it is full of #Ref! errors, which suggests it's drawing data from another workbook that we don't have.