Array Match and Return value upon multiple array match criteria

**VegetaOSX** · 03-04-2015, 05:32 PM

Hello friends, I have been been trying to do 2 different formulas without any luck for a month.

First Formula:

-If the values in COLUMN C4:C24 has a match in D30:D63, It should say" MATCH; else "NO MATCH.

IF(MATCH(C4:C25;D30:D63;0);"MATCH""NO MATCH") . I get VALUE ERROR.

Second Formula:
Return Value:IF values in C4:C24 and J4:J24 match values in D30:D63 and A30:A63 then RETURN the CORRESPONDING VALUE E30:E63. Should ignore empty value in J4:J24 if is blank.

=INDEX(E30:E63;MATCH(C4:C25;D30:D63;0)*MATCH(J4:J25;A30:A63;0)). I get NA ERROR.

NOTE: VLOOKUP and HLOOKUP, I tried...only works partially (Yellow col to yellow col). When I match yellow-green COL to yellow green col , I am making like UNIQUE values.

Hope you can help me! Thanks a lot in advance.

**Melvinrobb** · 03-04-2015, 05:55 PM

Take a look at the attached file, and see if the formulas return what you wanted.

**FDibbins** · 03-04-2015, 06:29 PM

Hi, welcome to the forum

IF(MATCH(C4:C25;D30:D63;0);"MATCH""NO MATCH") . I get VALUE ERROR.

Thats because MATCH needs a single cell/Item for the search, not a range.
Also, you left out a , between Match and No match.

Try this instead...
=IF(ISERROR(MATCH(D30,$C$4:$C$25,0)),"No MATCH","MATCH")

Same problem for the INDEX formula, plus you cannot combine criteria searches like that. Try this...
=IFERROR(INDEX($E$30:$E$63,MATCH(C4&J4,INDEX($D$30:$D$63&$A$30:$A$63,0),0)),"No Match")