1

I feel like I've been looking at this problem too long and am missing something obvious with my formula.

I have a spreadsheet that looks like this: Source Spreadsheet

I need to match the combination of values in cell A2 and B2 with the values in Column D and Columns F - I and populate cell C2 with the appropriate value from Column E.

Example: User 0bddb97e in A2 is assigned the REF 1102 in B2. User 0bddb97e is matched in D2 and D7 but REF # 1102 does not appear in row 7.

The result should be that cell C2 populates the Position Value P1 as the combination of USER ID and REF from A2 & B2 is a match to User ID in D2 and REF in F2 - thus using the cell value from E1

Correct Output

Unfortunately, the two INDEX/MATCH formulas I am using are returning a #N/A and #VALUE Error

Formula 1: Returns #N/A

=INDEX($E$2:$E$3,MATCH($A2,$D$2:$D$3,0),MATCH($B2,$F$2:$I$3,0))

Formula 1: Table Structure

Formula 2: Returns #Value

=INDEX(E2:E3,MATCH(A2&B2,D2:D3&F2:I3,0))

Formula 2: Table Structure

Any assistance is most appreciated.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
Eric Chesdale
  • 11
  • 2

4 Answers4

2

Match only will look at one column or one row. You will need something else.

=INDEX($E:$E,AGGREGATE(15,6,ROW($F$2:$I$8)/(($D$2:$D$8 = $A2)*($F$2:$I$8=$B2)),1))

enter image description here

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

I can think of a workaround, where you can concatenate columns using 'concatenate' function and then use a simple Vlookup to get the values.

Nitesh Choudhary
  • 11
  • 1
0

When you are doing index match match, the array for index needs to encompass the whole range of data. Instead try

=INDEX($D$2:$I$3,MATCH($A2,$D$2:$D$3,0),MATCH($B2,$F$2:$I$2,0))

The first match is looking up the information in D column, the second looking in row 2. But index needs to refer to the whole range.

Lux Claridge
  • 175
  • 3
  • 18
0

Enter as an array formula:

=INDEX($E$2:$E$8,MAX(($F$2:$I$8=B2)*($D$2:$D$8=A2)*(ROW($D$2:$D$8)-ROW($D$1))))

jblood94
  • 10,340
  • 1
  • 10
  • 15
  • 2
    One thing I have learned with INDEX using full column reference in the INDEX is not detrimental and it allows the dropping of the correction row `-ROW($D$1)` so : `=INDEX($E:$E,MAX(($F$2:$I$8=B2)*($D$2:$D$8=A2)*(ROW($D$2:$D$8))))` will be no slower or consume more resources than the longer formula. It will however recalc if data is in Column E below that and it changes, But normally it would not matter. – Scott Craner Jul 26 '18 at 22:07
  • @ScottCraner Makes sense. – jblood94 Jul 27 '18 at 00:10