# Lookup Specific Contents and Return

#### NaityP

##### New Member
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### CyrusTheVirus

##### Well-known Member
Sorry, please ignore my post.

Last edited:

#### CyrusTheVirus

##### Well-known Member
ok, try the below. Enter with Ctrl+Shift+Enter, not just Enter:

A1:
Code:
``=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>

Last edited:

#### NaityP

##### New Member
This worked perfectly - thank you so much CyrustheVirus!

NaityP

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

A1:
Code:
``=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

##### Well-known Member
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>

Last edited:

#### NaityP

##### New Member
Hello,

Code:
``=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>

Last edited:

Replies
35
Views
722
Replies
3
Views
100
Replies
0
Views
128
Replies
1
Views
265
Replies
3
Views
258

1,190,638
Messages
5,982,073
Members
439,753
Latest member
mnyankee

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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