Help needed with Excel formula

shland
shland used Ask the Experts™
on
Hi,

I have 2 applications, data was migrated from App1 to App2. each app has a gui (front end) and a db(back end). I have the db mapping between the 2 applications and from that I need to find the Gui equivalent field names.

Example:
here I have App1 Gui names and their equivalent label in the db as shown below: (this would be column A and B in my spreadsheet)
         App1      
Gui1                    DB1
Tag1       compn-name
Tag2       compn-service
Line no       line-num

here is the App2. Gui names and their equivalent label : (this would be column F and G in my spreadsheet)
              App2      
Gui2                             DB2
TagA                inst.tagnumber
Service                 inst.service
Line number         data.dx

here is the mapping between the databases: (this would be columns K and L in my spreadsheet).
         DB Mapping      
DB1                                  DB2
line-num                      data.dx
compn-name               inst.tagnumber
compn-service      inst.service

I need a formula that will find the Gui mapping between the 2 applications.
so the result should show me:

Tag1      TagA
Tag2      Service
Line no      Line number

Please specify the formula needs to be written in which cell?

Attached is my spreadsheet containing this information.

Thanks, much,
Excel-question.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
IS there anything common between the names in the GUI ??
This looks difficult

Author

Commented:
no names are different, otherwise I could have used vlookup...

Author

Commented:
I was thinking there should be a IF Statement we could write possibly?
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Can you please try the following:
Cell A9
=INDEX($A$3:$B$5;MATCH(K3;$B$3:$B$5;0);1)

Open in new window

copy cell A9 down to A10 and A11

Cell B9
=INDEX($F$3:$G$5;MATCH(L3;$G$3:$G$5;0);1)

Open in new window

copy cell B9 down to B10 and B11
I have attached the Excel file with the above formulas.
The advantage of using INDEX and MATCH compared to VLOOKUP is that you do not have to sort the lookup range.
Excel-question-1.xlsx
Excel VBA Developer
Top Expert 2014
Commented:
Here is an alternate solution which only requires one formula copied down.  

I re-arranged your app tables in a way that helps visualize the relationships better, moving from left to right you can see the fields line up:
example mapping
Since you already know your Gui1 values, you can insert them in J2:J4 and then add this formula in K2 and copy down:
=VLOOKUP(VLOOKUP(B3,$D$3:$E$5,2,FALSE),$G$3:$H$5,2,FALSE)

-Glenn
EE-Excel-question.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial