Link to home
Start Free TrialLog in
Avatar of cynx
cynxFlag for India

asked on

Match with multiple columns

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 ?
Avatar of Exceleved
Exceleved

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
SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cynx

ASKER

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
Avatar of cynx

ASKER

@ barryhoudini:,

I think you did the trick :), so if the value is not present the function will return 0, is that correct ?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cynx

ASKER

Thanks Barry and Alex for your help.