Solved

an alternative

Posted on 2013-05-28
4
125 Views
Last Modified: 2013-05-31
i am using the formula below to check if a name exits in two tables if ,the problem is that the function checks the columns four times two with the count if and the other to get the address on a large table this can affect performance.I need to only check the table once and return a blank if the name does not exist.
=IF(COUNTIF(EMP_NAMES,EMPLOY_EE),ADDRESS(MATCH(EMPLOY_EE,EMP_NAMES,0),1),IF(COUNTIF(SUPV_EMP_COL,EMPLOY_EE),ADDRESS(MATCH(EMPLOY_EE,SUPV_EMP_COL,0),COLUMN(SUPV_EMP_COL)),""))
0
Comment
Question by:Svgmassive
  • 2
4 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 250 total points
ID: 39202672
If you have 2007 or later you can try

=IFERROR(ADDRESS(MATCH(EMPLOY_EE,EMP_NAMES,0),1),IFERROR(ADDRESS(MATCH(EMPLOY_EE,SUPV_EMP_COL,0),COLUMN(SUPV_EMP_COL)),""))
0
 

Author Comment

by:Svgmassive
ID: 39202700
no some folks still have 2003
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39202746
Then try using two dummy columns with the two address functions

=ADDRESS(MATCH(EMPLOY_EE,EMP_NAMES,0),1)
=ADDRESS(MATCH(EMPLOY_EE,SUPV_EMP_COL,0),COLUMN(SUPV_EMP_COL))

and then something like

=if(iserror(col1),if(iserror(col2),"",col2),col1)

You can hide the dummy columns.
0
 
LVL 23

Accepted Solution

by:
NBVC earned 250 total points
ID: 39202796
Possibly an alternative for 2003?

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"",ADDRESS(MATCH(EMPLOY_EE,SUPV_EMP_COL,0),COLUMN(SUPV_EMP_COL)),ADDRESS(MATCH(EMPLOY_EE,EMP_NAMES,0),1)))
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now