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.

Improve this question

4 Answers 4

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

Comments

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.

Improve this answer

1 Comment

This will not return the desired result.
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))))

Improve this answer

2 Comments

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.
@ScottCraner Makes sense.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.