Help needed with Excel formula

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
shlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cool_apjCommented:
IS there anything common between the names in the GUI ??
This looks difficult
0
shlandAuthor Commented:
no names are different, otherwise I could have used vlookup...
0
shlandAuthor Commented:
I was thinking there should be a IF Statement we could write possibly?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

zalazarCommented:
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
0
zalazarCommented:
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
0
Glenn RayExcel VBA DeveloperCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.