Search on multiple criteria using excel vba

[Kriskros07]

Hi, I am in dire need of some assistance. I have scoured the existing posts but cant quite find the right post to twig with me what i need to do.

I am trying to write a macro which will search on 2 pieces of data taken from e.g. Row1, i.e. cells A1 and B1 and match those to 2 pieces of data (found together) in another part of the spreadsheet e.g. these might be in cells N7 and O7. I want the code to return the position of the matched data (presumably using Match) - so this would be 7 if it matched the data with cells N7 and O7(it could be matched anywhere in these columns of course). A seperate issue - but just to explain why i am trying to do this - I will subsequently use this positioning to compare the remaining columns of data (e.g. compare in this instance A1:M1 against N7:Z7).

A Single criteria is easy enough:
where a is the value found in cell A1
relpos = Application.Match(a, Range("N:N"), 0) 'single criteria

however, i need to add in cells B1 to the reference argument and Range O:O to the lookup array.

I believe i need to use the Evaluate function also to do this in VBA. And Index maybe??

Any help will be very much appreciated!



Kind Regards,
Chris

 

[Saagar]

Just thinking out aloud - is it an option to insert a column at C and concatenate A and B, then insert a column at P and concatenate N and O? Then you can match between C and P with single criteria.

 

[patrickmuldoon99]

Second that suggestion;

Whether in code (in which case you should create your own internal data structure of concatenated search parameters and search sets) or by, as suggested, concatenating the search parameter and search set array.

 

[Kriskros07]

I cant insert a column as the purpose of the exercise is to compare baseline and actual results side by side. However, i can try to create a concatenated array and look for the concat reference variables within this.

Thanks for your suggestions anyway much appreciated!

 

[GTO]

Cross-Posted: http://www.vbaexpress.com/forum/showthread.php?p=249766#post249766

 

[Saagar]

If you are unable to insert columns and compare, then the other way is to use CSE formula as suggested in the other VBAExpress message board.

Ordinary formulas perform one-dimensional look ups, CSE enables inclusion of arrays as parameters in excel formula and perform 2-dimensional look ups.