vba lookup logic for multiple tabs

cvI have a spreadsheet I am working on to look up a zip code amongst multiple preset zip code ranges, and return the rate of a service from multiple providers (for analysis/comparison use) to that zip code. The kicker for me, was that I have 4 different points of origin, and I don't think I figured out the variables correctly, and 2 of these origins are supposed to look up different data from 2 different tabs. So it's supposed to work like this:

Please Login or Register  to view this content.

I kind of have two questions.
1) What am I missing to make the code below work correctly? (I can provide my mockup file if need be)
2) How would I do this looking up the providers dynamically, and not hard coded like I have below?

I apologize for all the comments, that was a co-worker and myself trying out different ideas..

Please Login or Register  to view this content.

Note*** - All tabs with the rates follow the same format...zip codes are column 'A', providers are listed row 'A', and start displaying rates at column 'F'.
The only difference between the tabs, are some tabs don't have the same ranges as the others, and the providers "may" also different, some tabs its the same, some they are different, or just not as many.

Help from anyone?

Testdummy.zip

File attached.

Originally Posted by protonLeah

I don't know what your goal is from looking at the workbook. There are no comments and no indicators of desired results.

Well, back to my first post, unless this is too confusing for you..

Please Login or Register  to view this content.

Basically, "Target and Actual table" is the tab that has two values the macro needs to look at. First, is the 'origin' (column A) which should tell the macro which of the other tabs to look at for rates. The second is the 'postal code' (column D), which give the macro the single number to look up on the tab specified by the 'origin.' The postal code being looked up, will be found within postal code ranges on the numbered tabs (5,12,22,22B,30, and 30B) in column A of those tabs.

The objective is to have all the columns on the 'Target and Actual table' with a name/number on them (Column G, I, K, M, O, etc.) to have the rates from that person (or number) from that particular 'origin' filled in. (I say person or number, because they are both unique identifiers to each other, so it just a matter of preference, for my actual project, it would be easier to use the numbers (row 3) as some of the actual names are quite long)

The spreadsheet is to be used as a cost comparison analysis between all 'Names' from each origin, to the zip code being looked up. The reason there are two tabs that have the letter 'B' in them( 22B, and 30B), is because two of the origins have two different sets of 'names' with different postal ranges, so if the 'origin' is '30', then the zip code would need to be looked up on both tab '30', and '30B', however many postal codes will not be found on the "B' tabs (which is fine,) in which case I would want a value of $0.00 returned for rates not found on these tabs.

Hope this helps you understand it a bit more.