1

Afternoon fellow Spicees.

I’m looking for some quick help with the attached spreadsheet.

I am looking to populate Column V in the first tab called ‘2 Col Run List’

Column B in the first tab is a Job Number which correlates with the tab called ‘SBS Jobs’ (Column J) - Note that only the first 5 digits are the actual Job Number itself.

What im looking to do is add to Column V in the first tab, the contents of Column I from the SBS Jobs tab.

e.g Add the text ‘69845 - 22707 due 18/11’ to the row which contains Job 69845

For the life in me I cant get this working and am going round in circles. Any help appreciated

Thanks

MMTest_Overprint_Schedule_August_2014_3.xlsm (686 KB)

3 Spice ups
2

Does this do what you want?

Put this in V89 to apply to that line

=CONCATENATE(B89, " ", ‘SBS Jobs’!I10)

1 Spice up
3

The fact you’ve got duplicated values in your lookup table isn’t going to do you any favours if you try to go down that route.

4

In addition VLOOKUP only work with the first field being in ascending order without duplicates

1 Spice up
5

First field, Yes. Without duplicates, Yes. But Vlookup hasn’t had to have the table sorted for a while now, provided you are looking for an exact match.

It’s only Lookup that needs the table sorted.

See LOOKUP function - Microsoft Support

6

Maybe I’m just crazy this morning, but in your example, Job 69845 isn’t even in the first sheet. Unless it was just an example that doesn’t exist yet

7

It grabs the first one it finds, IIRC

8

Fair enough, perhaps just something I’ve always done.

Either way lets see if the OP gives more info

9

Two problems I can see

Vlookup requires the value you are searching for to be in the LEFTMOST column of the specified table_array

You cannot Vlookup values in column J and return values in Column I. You’ll need to move the values to Column K

As others have pointed out the jobno values in column J are not unique. If you set the range_lookup parameter of Vlookup to FALSE you’ll force it to find an exact match but it will return the corresponding value from the first match it finds. This may cause odd behavior and is not guaranteed to be the first instance of the number in row order (due to the way searches are carried out)

10

Did the trick completely, legend

11

Who?

Don’t forget to mark helpful and best answers

12

While this is correct, in that VLOOKUP can only search on the LEFTMOST column, you could use an INDEX MATCH instead, so the columns could remain in position (see this topic ).

13

I agree with Jeremy, I don’t find the jobid 69845