Hello all,

I have currently have 3 sheets: sheet 1 is where the user can input data (max of 12 items from already created dropdowns), sheet 2 would essentially copy and paste the user entries from Sheet1 (in specific places, shown in attached spreadsheet), sheet3 holds 3 of data with defined names: states, cities, and names. Sheet 1 and Sheet 3 should talk together and tell sheet 2 where organize the data.

There are a few conditions to this (also typed out in Sheet2 for reference):
1) Only work if cell C1 is YES
2) Use the defined names from Sheet3
3) The cells must be either return a name, city, or state, or be blank. Cities should be returned on sheet2 in cells A4:A12, States in A15:A23, and Names in A26:A28
4) (haven't figured this one out) the non blank cells MUST be listed first, with the blank cells (aka what #N/A would be) listed after


Previous attempts:
Hours of attempting to write VBA code until I realized that this could be done without VBA
where I got without VBA:
Please Login or Register  to view this content.
This worked great, but the user is able to pick the items on sheet1 in any order, and the lists on sheet 3 are always changing (already coded in VBA such that a superuser can enter new items and the defined name will auto update)

The biggest issue I am having is that I am able to do this very simply using excel 2013 by using a vlookup and comparing 2 columns.

The kicker:
BUT, the spreadsheet I am working is an .xls with macros and is in 97-2003 compatibility mode. I can't figure out how to code this in the older version of excel, as you can't compare 2 columns of data with vlookup in old excel. I also am not able to convert the file into a newer version. Any help is greatly appreciated!! testvlookup.xls