cynx
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 ?
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 ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
@ barryhoudini:,
I think you did the trick :), so if the value is not present the function will return 0, is that correct ?
I think you did the trick :), so if the value is not present the function will return 0, is that correct ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Barry and Alex for your help.
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