Excel index match formula to pull data from another tab/file

Question

I am trying to use Excel's index match functions to pull data from one spreadsheet into another. For now, I am trying to get it to work with the data in a separate tab, but eventually I would like to pull the data in from a separate file.

The spreadsheet with input data looks like this:

The spreadsheet with the output/formulas looks like this:

This is the formula that's in cell B2 of the output spreadsheet, but it's returning a #VALUE! error:

=INDEX(extract!$D$2:$D$2000,MATCH($B1&$A2,extract!$A$2:$A$2000&extract!$B$2:$B$2000&extract!$C$2:$C$2000,0),1)

I want to look things up using multiple keys. Would SUMIFS still apply? — b00kgrrl
– b00kgrrl, Commented Sep 17, 2014 at 14:54

CallumDA · Accepted Answer · 2014-09-17 14:55:12Z

1

See my solution below, SUMIFS() works because it allows you to get the sum of a column based on multiple criteria:

=SUMIFS($D$2:$D$7,$C$2:$C$7,$F2,$B$2:$B$7,G$1)

You can drag the formula across and down

enter image description here

answered Sep 17, 2014 at 14:55

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

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

3 Comments

b00kgrrl Over a year ago

The above solution seems to be hard coded to specific cells. I need to search column D (student_age_count) to find a value that matches whatever is in columns A, B and C.

CallumDA Over a year ago

It is hardcoded for the example because it needs to be hardcoded that way so that you can drag it from cell G2 to cell I7. The format for the SUMIFS() formula is as follows: SUMIFS(Sum_range, range_1, criteria_for_range1, range_2, criteria_for_range2). Easier than rewriting the formula for your cell references is just pressing F2 button on cell G2 and dragging my ranges to suit yours

user4039065 Over a year ago

=SUMIFS(Extract!$D$2:$D$7,Extract!$C$2:$C$7,$A2,Extract!$B$2:$B$7,B$1)

user4039065 · Accepted Answer · 2014-09-17 14:58:49Z

0

In the receiving worksheet's B2 use this standard (non-array) formula.

=IFERROR(INDEX(Extract!$D$2:$D$7,MIN(INDEX(ROW($1:$6)+((Extract!$B$2:$B$7<>B$1)+(Extract!$C$2:$C$7<>$A2))*1E+99,,))),0)

Fill both right and down.

enter image description here

answered Sep 17, 2014 at 14:58

user4039065

Collectives™ on Stack Overflow

Excel index match formula to pull data from another tab/file

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related