Lookup from one worksheet to the next across several different parameters

[dianaCatz]

In the attached file, I need to match on "total capacity" worksheet - the name in column B to the year - 2019, to the tab marked with that year - to the SHIPS column. So in 2019, ships is column D.
The issues are:
Some years have additional names that aren't in my original list.
Is there also a way to highlight or identify those that weren't on the original list.
If not , that is okay, and even just to fill this in as is would really help.

[azumi]

Not sure but try, put this on C3 (Sheet "Total Capacity") as Year Criteria :

=TRIM(RIGHT(SUBSTITUTE(TRIM(B3)," ",REPT(" ",255)),255))+0

And this on C9 (Sheet "Total Capacity") then copied down and cross :

=IFERROR(IF($C$3=C$8,INDEX(INDIRECT("'"&$C$3&"'!D3:G500"),MATCH($B9,INDIRECT("'"&$C$3&"'!$C$3:$C$500"),0),MATCH(LOOKUP(2,1/NOT(ISBLANK($C$7:C$7)),$C$7:C$7),INDIRECT("'"&$C$3&"'!$D$2:$G$2"),0)),""),"")

[nigelbloomy]

This formula in D9 seems to work. I was able to drag it over and down. It doesn't have the trim function like azumi suggests though.

Please Login or Register  to view this content.

[nigelbloomy]

In order to catch new company names, I don't know how to do it on the main "Total Capacity"sheet, but I added this formula to B3 in each of the year tabs. It just shows a FALSE if something is not on the main sheet.

Please Login or Register  to view this content.

[dianaCatz]

This formula in D9 seems to work. I was able to drag it over and down. It doesn't have the trim function like azumi suggests though.

Please Login or Register  to view this content.

Hi Nigelbloomy, how would I adjust your formula to get berths in column E? Or capacity in column F?

[nigelbloomy]

The simplest way would be to use this in D9 for "Ships"

Please Login or Register  to view this content.

Then for "Berths" it would be the same formula, but change the 2 to a 3. Capacity would be 4, and Market Share would be 5.

It is possible to have some IF statements that populate the column based on the name in row 7, or you could even search for "Ships" or "Berths" in each tab to find the correct column. But if they generally stay the same, then this vlookup with indirect is a much easier way to go.