We help IT Professionals succeed at work.

Match with multiple columns

285 Views
Last Modified: 2013-11-25
currently i am using MATCH formula to find if a value is present in a array of column.
However the array in which i want ot search can increased upto one hundred thousand records or more, hence cannot store these in one column.

Hence i need to modify my match formula to look in Table array which can be present over multiple columns (columns may increase).

Since MATCH only works with one column, what is an alternative ?
Comment
Watch Question

Hi cynx,

it can be done by (simple) macro or by (complicated) worksheet function. I could suggest if you provide more info.
In particular - MATCH returns single value and this new function will return two. How it fill be fit in your project?

Also you may move to XL2007, it keeps over 1 million rows in a sheet.

Alex
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Alex,

Excel 2007 migration not possible for me at the movement.

I have choose not to use macro looping (for example using find function in a loop) because formula was working faster for me at moment.

What i am looking for is, I have a list of numbers in a worksheet (WS A). I have another worksheet  (WK B) in which i have dump of numbers, these number increases each time (they are populated via macro). If the row number reaches 65536, they are populated in new column and so on...

I need to find if the number in WS A is present in WS B dump of numbers.
Currently i was applying MATCH formula via .formulaR1C1 in macro, in WS A to find if the number is present in WS B or not. But initially i had only one Column.

Now since the columns can be multiple i need to look in array of multiple columns.
How can this be achieved ?

Thanks in advance

Author

Commented:
@ barryhoudini:,

I think you did the trick :), so if the value is not present the function will return 0, is that correct ?
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
so if the value is not present the function will return 0, is that correct ?
Yes, If you use COUNTIF you just get a count of the times the value appears in the specified range, in the IF formula above zero will result in FALSE (and therefore "Not Present")  and any non-zero value will be TRUE (and therefore "Present")
barry
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Barry and Alex for your help.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.