UDF to make a double vlookup and change the cell background color

Dear Community:

I hope you all are having a wonderful day! Hope you could please help me to find a solution (if any)

I have to do +500 analysis of locations, each location will have a independent sheet, with it's own elements and variable table length.

Each sheet has:
UPPER TABLES: all the upper tables have the same structure since they come from a pivot table (they are "official" tables, with name and all). These tables contain 3 main columns, Shape ID (Column D), a "Real" Name (Column E) and a ShortCode (Column F).
All elements in Shape ID, and "Real" exist, but not all ShortCodes exists. Please see the image below.

Proper_Table.png

There is also a "manual table" below each "official table". This table was created manually relating Shape IDs from the "official table" above. This "manual" table also is consistent across all the +500 locations.

What I need to do is:

With a VLOOKUP, using SHAPE ID in column B from the "manual table", create a new "manual table" with the SHORTCODE. If the Shortcode does not exist, I need to do another vlookup and this time, bring the "real name" and highlight the cell with yellow. I've doing it manually and I got dizzy after location 50 hehehe.

Is there a way to do a UDF to do both vlookups and the backgroud color change based on the result of the first lookup? It would be ideal if the UDF asks for the cell to evaluate as input. The "official" table to use could be defined by default as the only table that exist on each sheet and program the rest of the vlookups (it's always the same formula, its always the same "active table", its always the same columns, etc). Hope you can help me, please my soul is dying here.

Attached is the sample file. With the final result.



Thank you so much in advance! Have a nice weekend!

Please see update below. Thanks!

I could not find a way to do it through a function, I think it is not possible due to the nature of the UDF. But I created a sub that helped me. It's a two step process but it's something. If anyone has some similar issue, I got this:
Module 1 (perform a vlookup only if the middle table has an empty cell)

Please Login or Register  to view this content.

Module 2: Color the cells that were empty in the middle table for the final result.

Please Login or Register  to view this content.

Maybe there's a better way to do it but I really couldn't find it. Hope this can help someone in disgrace like me hehe