2

I'm struggling a little bit with Excel MATCH function (Excel 2016). I'm able to use the basic MATCH when comparing one column to another, but lacking the ability to compare 2 sets of columns at the same time:

Value_1 Value_2   Comp_1   Comp_2

700     A24          555    C2
750     A34          620    B77
620     B77          700    A24
555     C1           750    D9

I would not have a problem in comparing Value_1 (column A) to Comp_1 (column C) and Value_2 (column B) vs Comp_2 (column D) separately with:

=MATCH(A1;C$1:C$4;0)

However what I need, is to compare both columns Value_1 AND Value_2 to the columns Comp_1 and Comp_2.

For an example A1&B1 are matched in C3&B3, however there is no match for A2&B2. Is there a way to run a MATCH function for that?

I am aware that if I would utilize extra columns to combine values in tune of =A1&B1 & =C1&D1 and then proceed to run simple MATCH formula known to me for this new combined data, it should work, however I'm looking if there is a way to compare those extended ranges without combining them in extra space. Ideally with MATCH, which is "close to the vest" or other formulas if need be.

Thanks!

sql scholar
  • 199
  • 1
  • 2
  • 13
  • What are you returning? MATCH returns the relative position, are you using that to return something else? – Scott Craner Nov 01 '17 at 22:47
  • here are some methods: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another – Scott Craner Nov 01 '17 at 22:50
  • You can concatenate A&B, and C&D, as inputs to `MATCH`. For example `MATCH(A1&B1, C$1:C$4&D$1:D$4, 0)`. – xidgel Nov 01 '17 at 22:51
  • Essentially the key is to know weather there is a match or not. So if a match is found, relative position is irrelevant (other than the fact that a match is found). If no match is found and a #N/A error is displayed then that's also good enough. – sql scholar Nov 01 '17 at 22:52
  • xidgel, unfortunately it would not work, also Excel is rejecting commas in the formula. – sql scholar Nov 01 '17 at 23:11
  • What's wrong with `COUNTIFS(...)>0`? –  Nov 01 '17 at 23:17
  • Get used to having commas supplied as the computer's regional list separator in place of your computer's regional semi-colon. The EN-US standard is a comma, other regional areas (particularly Euro-based) are semi-colons. People give you the solution that works on **their** system and may not take the time to translate for you. Be aware that in an international forum, you will generally receive answers that follow a EN-US regional system. –  Nov 01 '17 at 23:21
  • Jeeped, thanks for chiming in. That's understandable, however swapping commas with semi-colons did not help and that was not an obstacle, just a sidenote. – sql scholar Nov 01 '17 at 23:23
  • @xidgel forgot to mention that was an array formula and requires CSE to work properly. –  Nov 01 '17 at 23:25

1 Answers1

1

Try,

=countifs(a:a, c1, b:b, d1)>0
'in non-EN-US
=countifs(a:a; c1; b:b; d1)>0

TRUE if found, FALSE if not. Or ...

=sign(countifs(a:a, c1, b:b, d1))
'in non-EN-US
=sign(countifs(a:a; c1; b:b; d1))

1 if found, 0 if not. Or ...

=countifs(a:a, c1, b:b, d1)
'in non-EN-US
=countifs(a:a; c1; b:b; d1)

True numeric count of matches if found, 0 if not.

  • Thanks a lot everyone contributing and especially Jeeped. Have to tinker with it a little bit, but looks like I can use COUNTIFS and can also familiarize myself with a new Excel formula which is always great. Much appreciated! – sql scholar Nov 01 '17 at 23:38
  • fwiw, functions like the newer countif/countifs or sumif/sumifs can use full column references without detriment unless there is something below the relevant data that could foul up the result. –  Nov 01 '17 at 23:46