VLOOKUP maybe??

[JakeSherlock]

So, I have a file with values in one column (Column 1) and values in a second (Column 2). On another sheet I have a list I need to make from those two columns (Column 3).

Column 1 has info as :

201 - 1
201 - 1
202 - 2
202 - 3
etc.
and is random but in chronological order, there are multiples of the same number but correspond with column 1 which is all different. The info is in the order it needs to be sorted into column 3, one after the other in chronological order. Column 2 = B1:B3 are 201 - 1 and column 1 = A1:A3 are 254, 647, 0147.

The second column is the info that will be in the list I am generating, totally random numbers. Hence VLOOKUP.

The VLOOKUP works great but on the second cell down in my new list I want VLOOKUP to skip the above number if the same and find the next in column 2.

Code is:=VLOOKUP("201 - 1",Reliefs.xlsx!Table1[[Block]:['# Working]],2,FALSE)

[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3 (201 - 1 #s)[/TD]
[/TR]
[TR]
[TD]1363[/TD]
[TD]201 - 10[/TD]
[TD]Code Generates: 0105[/TD]
[/TR]
[TR]
[TD]0215[/TD]
[TD]201 - 11[/TD]
[TD]I want 1361 not 0105 again[/TD]
[/TR]
[TR]
[TD]0025[/TD]
[TD]202 - 50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0091[/TD]
[TD]201 - 12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0105[/TD]
[TD]201 - 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0005[/TD]
[TD]201 - 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1315[/TD]
[TD]202 - 42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0103[/TD]
[TD]201 - 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0806[/TD]
[TD]202 - 43[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0047[/TD]
[TD]201 - 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1340[/TD]
[TD]202 - 44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0731[/TD]
[TD]201 - 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0300[/TD]
[TD]202 - 45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0122[/TD]
[TD]202 - 46[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0149[/TD]
[TD]202 - 47[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0260[/TD]
[TD]202 - 48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0909[/TD]
[TD]201 - 8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0354[/TD]
[TD]202 - 49[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0998[/TD]
[TD]201 - 9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0971[/TD]
[TD]201 - 10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0261[/TD]
[TD]201 - 11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1299[/TD]
[TD]202 - 41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0289[/TD]
[TD]201 - 12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0296[/TD]
[TD]202 - 42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0536[/TD]
[TD]202 - 43[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1361[/TD]
[TD]201 - 1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Help in making VLOOKUP skip would be great

-Jake

 

[jarjarbingie]

say the table above are in cells A1:B27:

C2 = INDEX($A$2:$A$27,SMALL(IF($B$2:$B$27="201 - 1",ROW($A$2:$A$27)-1,""),ROW()-1),0)

press CTRL+SHIFT+ENTER

 

[JakeSherlock]

I double checked.. Seems to hiccup here. Thanks for the help.

 

[JakeSherlock]

Wait. Just tried Ctrl+Shift+ Enter. NUM! error.

 

[jarjarbingie]

what's your exact formula? it seems that it didn't find a match..

 

[JakeSherlock]

=INDEX(Reliefs.xlsx!Table1['# Working], SMALL(IF(Reliefs.xlsx!Table1[Block]="201 - 1", ROW(Reliefs.xlsx!Table1['# Working])-1, ""), ROW()-1), 0)

The 201 - 1 is suppose to be 201,space,-,space,space,1. Double checked.

# Working = Random numbers
Block = 201 - 1, etc.

-J


[TABLE="width: 250"]
<colgroup><col></colgroup><tbody>[TR]
[TD]0034[/TD]
[TD]<- VLOOKUP working[/TD]
[/TR]
[TR]
[TD]#NUM![/TD]
[TD]This formula not[/TD]
[/TR]
</tbody>[/TABLE]


=VLOOKUP("201 - 1",Reliefs.xlsx!Table1[[Block]:['# Working]],2,FALSE)

 

[jarjarbingie]

the formula I have provided assumes that the formula is in cell C2 and since it uses ROW() function, it needs to be adjusted accordingly.. in the screenshot you have provided the ROW()-1 gave a 6, if the formula is in row 7, change -1 to -6.. for the ranges it's hard to tell because you are using the table format..