Solved

an alternative

Posted on 2013-05-28
4
127 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

808 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