1

I have a huge challenge in excel.

For the following logic of array and match:

    {=INDEX(array, MATCH(lookup_value, lookup_array, match_type))}

How may I set "lookup_value" as a concatenated "array"?

For example: A2&B2:B10, and that it look into the lookup_array, as A2&B2; A2&B3;...,A2&B10. And finally returned tha concatenated value that matched the lookup_array.

My case is shown in the attached image.

case

Jose Alberto Salazar
  • 79
  • 3
  • 13
  • With your example you are looking up and returning the lookup value as you are searching the same array. so not sure what you really want. can you mock up some data and expected outcome? – Scott Craner May 24 '17 at 20:13
  • Could it be that you are looking for something like this: https://stackoverflow.com/questions/42695455/control-database-form-excel-through-vba/42720826#42720826 – Ralph May 24 '17 at 20:31
  • Hi, I just attached an image to my original post. It shows what I'm trying to accomplish. – Jose Alberto Salazar May 24 '17 at 21:16
  • You will have 8 or 9 lookup return values(depending on which example), some of which might be #N/A. If you need the first one matched, then wrap the match with `IFERROR(INDEX(Process!$C:$C, MIN(IFERROR(MATCHFUNCTION, 1E9))), "Not Found")`, where MATCHFUNCTION is the entire match construct – MacroMarc May 24 '17 at 21:25
  • MacroMarc... your solution worked!.. thanks for the help. – Jose Alberto Salazar May 24 '17 at 21:57

1 Answers1

0

Put this formula in B2 on Material and copy/drag down:

=INDEX(Process!B:B,AGGREGATE(15,6,ROW(Process!$A$5:$A$8)/((Process!$A$5:$A$8=A2)*(ISNUMBER(MATCH(Process!$C$5:$C$8,Station!$A:$A,0)))),1))

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81