Okay, this sounds really strange but I have a Worksheet that follows a theme:
Seems confusing, but:
Col1 are Parent "National"
Col2 are "Regional" (Col3 a reference number and Col4 the primary key)
Col5 are "Local" (Col6 a reference number and Col7 the primary key)

Why you might ask? Well when users double click cell in the "A" colum of all other worksheets, a user form pops and present them with a selection list from COL1 of the Sheet above. Once selected a nested IF statement will result the matches from COL2 and another IF statement will yield way to options from COL5. The result is then entered into the A cell with all three selection in a comma delimited format.


I have the selection held as a variable and I now need to use it to "lookup" the corresponding "primary key". Meaning that I need to:
Search All of the cells in Range A1:H200 to find the match (which will always exist) and then record the COL4 or COL7 result in a hidden field. So, if the users only selects a Region, then I need COL4 in cell "F" or if they select a Local, then I need COL7 in cell "F".


I think that there is a better way of doing this, but I've go so many issues going on right now that I can't seem to think straight.

OKay, there was a really easy fix for this. I created and named "List", then used VLOOKUP to find the match and pass back the primary key.

So, Col2-Col4 became RegionList and then I was able to call on the primary by using =VLOOKUP(A2, RegionList, 3, False) which looks up the entry in Cell A2 in my RegionList and returns the corresponding value in col4 (which is the 3rd column in the Regionlist). False, merely states that there must be an exact match.

Hope this helps someone else!