Lookup Specific Contents and Return

[NaityP]

Hello,

I have a Column of data that contains headers throughout ("Job #") and a large number of repetitive data points ("Cost Codes"). For example:

	Column A	Column B
Row 1		Job 1111
Row 2		Cost Code 1
Row 3		Cost Code 2
Row 4		Cost Code 3
...		...
Row 8		Job 2222
Row 9		Cost Code 1
Row 10		Cost Code 2

<tbody>
</tbody>

I would like to write a formula into every cell in column A that looks for and returns the most recent Job # in column B. In the example above, cells A9 and A10 should produce "2222", where as A2:A4 should all return "1111".

Does anyone have any idea? I can not seem to think of a formula that looks bottom up.

Thank you in advance for your help,

NaityP

 

[CyrusTheVirus]

Sorry, please ignore my post.

 

[CyrusTheVirus]

ok, try the below. Enter with Ctrl+Shift+Enter, not just Enter:

A1:

=IF(ISNUMBER(SEARCH("job",B2)),"",INDEX($B$2:B2,LARGE(IF(IFERROR(SEARCH("job",$B$2:B2),FALSE),ROW($B$2:B2)-ROW($B$2)+1),1)))

Column A	Column B
	Job 1111
Job 1111	Cost Code 1
Job 1111	Cost Code 2
Job 1111	Cost Code 3
	Job 2222
Job 2222	Cost Code 1
Job 2222	Cost Code 2

<tbody>
</tbody>

 

[NaityP]

This worked perfectly - thank you so much CyrustheVirus!

NaityP

ok, try the below. Enter with Ctrl+Shift+Enter, not just Enter:

A1:

=IF(ISNUMBER(SEARCH("job",B2)),"",INDEX($B$2:B2,LARGE(IF(IFERROR(SEARCH("job",$B$2:B2),FALSE),ROW($B$2:B2)-ROW($B$2)+1),1)))

Column A	Column B
	Job 1111
Job 1111	Cost Code 1
Job 1111	Cost Code 2
Job 1111	Cost Code 3
	Job 2222
Job 2222	Cost Code 1
Job 2222	Cost Code 2

<tbody>
</tbody>

 

[CyrusTheVirus]

You're welcome, glad it worked.

The formula assumes that Job1111 starts in cell B2 (row 2) not B1, just fyi. that's why my formula references B2 and not B1, because I should've put the formula start in A2, not A1, make sense? I hope, like this...

Input formula in A2 (not A1):

Row 1	Column A	Column B
Row 2		Job 1111
Row 3	Job 1111	Cost Code 1
Row 4	Job 1111	Cost Code 2
Row 5	Job 1111	Cost Code 3
Row 6		Job 2222
Row 7	Job 2222	Cost Code 1
Row 8	Job 2222	Cost Code 2

<tbody>
</tbody>

 

[NaityP]

Hello,

=IF(ISNUMBER(SEARCH("job",B2)),"",INDEX($B$2:B2,LARGE(IF(IFERROR(SEARCH("job",$B$2:B2),FALSE),ROW($B$2:B2)-ROW($B$2)+1),1)))

Is there a way to change the above formula so that it looks bottom up? In other words, have it produce the following:

Row 1	Column A	Column B
Row 2		Job 1111
Row 3	Job 2222	Cost Code 1
Row 4	Job 2222	Cost Code 2
Row 5	Job 2222	Cost Code 3
Row 6		Job 2222
Row 7	Job 3333	Cost Code 1
Row 8	Job 3333	Cost Code 2

<tbody>
</tbody>

Thank you,

NaityP

You're welcome, glad it worked.

The formula assumes that Job1111 starts in cell B2 (row 2) not B1, just fyi. that's why my formula references B2 and not B1, because I should've put the formula start in A2, not A1, make sense? I hope, like this...

Input formula in A2 (not A1):

Row 1	Column A	Column B
Row 2		Job 1111
Row 3	Job 1111	Cost Code 1
Row 4	Job 1111	Cost Code 2
Row 5	Job 1111	Cost Code 3
Row 6		Job 2222
Row 7	Job 2222	Cost Code 1
Row 8	Job 2222	Cost Code 2

<tbody>
</tbody>