In the attached there are two solutions on Sheet1. A simpler one (column F) that gives the same results as you expect from the data you provided, but on a test sheet (Sheet1 (2)) gives different results (see cell F407).


This is the of formula:
=INDEX($N$2:$N$13,MATCH($B2,$M$2:$M$13))


The more complex formula (column E) uses a different lookup table:
=IF(LEFT($B2,1)="B","BRYAN",IF(LEFT($B2,1)="U","AL MEDA",INDEX($K$2:$K$11,MATCH(VALUE(RIGHT($B2,3)),$ J$2:$J$11))))
gives more consistent results (see cell E407).


On Sheet1 there are comments in cells J2 and M2 regarding setting up the lookup tables.


It should be enough to get you started.

Attached Files

msOfficeForums39050Table Example.xlsx (30.2 KB, 11 views)

oh.... wait I have one more question if you dont mind.... Sooooo If I already had a complete list that had all possible accounts listed and their respective plants, that would make it more consistent? Could you please show me one more time? On the attached file, the first list is the report without plants, on the 2nd tab is the complete department list with all possible entries.

Attached Files

List.xlsx (28.1 KB, 9 views)

Yes, the full list of all possible corresponding codes and plants will be the most consistent, and if there's a mistake in any code on Sheet2 you will be alerted to that fact by an error in the plant cell - which could be useful; it could make you ask the question do we have a new dept code? or has someone made a mistake?


It's a balancing act; do I have a long list (more difficult to maintain when there are department changes) or a shorter list where we know a given plant will be associated with a range of numbers (this is often the case with cost centre names)?
If you are going to use a full list then the 3rd argument of the MATCH function has to be 0 (it looks only for an exact match) and the dept numbers can then be in any order. Also it's easier to write the formula if you use VLOOKUP with the last argument being FALSE (also an exact match).

The two formulae are in columns D and E of Sheet2 of the attached.


If you want a friendlier thing to show than an error code, then you can enclose the formula in the IFERROR function, see cells F42 and F43 of Sheet2.

Attached Files

MSOfficeForums39050List.xlsx (30.2 KB, 9 views)

I used VLOOKUP and i think I got it to work.

My last task to add to the macro is to give me total for each plant. I hate to ask again, but it would be my last question I promise!

whats the best way to do that? thanks again for everything!

Attached Files

list2.xlsm (37.5 KB, 9 views)