NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

**t1gereyez** · 05-31-2017, 03:18 PM

Hi, newbie here. I am attempting to attach a spreadsheet where I am failing at teaching myself the Index, Match, Match.

issues: first being, I am trying to use the formula to match a vertical, then horizontal and from the matching horizontal, do a vertical. so three criteria, not tied to a column number as the data moves from column to column. Market Cap on Sheet 2

Secondly, using the Index, Match, Match, how do I get all instances of the first vertical lookup? Issues Names on Sheet 3.

Thank you in advance!

**mcmahobt** · 05-31-2017, 03:36 PM

In F4, entered as an ARRAY formula with CTRL+SHIFT+ENTER:

Please Login or Register  to view this content.

In E11, also entered as an ARRAY formula:

Please Login or Register  to view this content.

**63falcondude** · 05-31-2017, 03:37 PM

Try this in F4 of Sheet1:

=INDEX(Sheet2!$F$2:$F$7,MATCH($A$2&$E4,Sheet2!$A$2:$A$7&Sheet2!$E$2:$E$7,0)) Ctrl Shift Enter

Drag through F9.

**mcmahobt** · 05-31-2017, 03:43 PM

Originally Posted by 63falcondude

Try this in F4 of Sheet1:

=INDEX(Sheet2!$F$2:$F$7,MATCH($A$2&$E4,Sheet2!$A$2:$A$7&Sheet2!$E$2:$E$7,0)) Ctrl Shift Enter

Drag through F9.

Note that this method may be problematic depending on how the data is moved as OP described. If values (not cell references) are moved, this formula will return incorrect results since it is not matching to a column header.

**t1gereyez** · 05-31-2017, 03:50 PM

This is great - wondering if there is a way to tie it to the column header (ie" market cap band name") rather than column letter in case the source data file is edited.

**63falcondude** · 05-31-2017, 03:52 PM

Originally Posted by mcmahobt

Note that this method may be problematic depending on how the data is moved as OP described. If values (not cell references) are moved, this formula will return incorrect results since it is not matching to a column header.

Originally Posted by t1gereyez

This is great - wondering if there is a way to tie it to the column header (ie" market cap band name") rather than column letter in case the source data file is edited.

I don't understand the "moving values" part.

With an INDEX MATCH formula, the ranges in the formula change accordingly when you move the data around.

**leelnich** · 05-31-2017, 03:54 PM

Here's a regular (non-array) formula for F4 and copy down:
=SUMPRODUCT((Sheet2!$A:$A=Sheet1!$A$2)*(Sheet2!E:E=Sheet1!$E4),Sheet2!F:F)

**mcmahobt** · 05-31-2017, 04:00 PM

Originally Posted by 63falcondude

I don't understand the "moving values" part.

With an INDEX MATCH formula, the ranges in the formula change accordingly when you move the data around.

If the data range needs to be expanded, and data fields moved manually through a method such as copy > paste values, references to the correct field are then lost. This is what I interpreted as OP wrote, "...not tied to a column number as the data moves from column to column".

**63falcondude** · 05-31-2017, 04:00 PM

Originally Posted by leelnich

Here's a regular (non-array) formula for F4:
=SUMPRODUCT((Sheet2!$A:$A=Sheet1!$A$2)*(Sheet2!E:E=Sheet1!$E4),Sheet2!F:F)

It's never a good idea to use whole column references with a SUMPRODUCT formula.

You can use SUMIFS to do the same thing:

=SUMIFS(Sheet2!F:F,Sheet2!$A:$A,$A$2,Sheet2!E:E,Sheet1!$E4)

As long as there is no more than one match (multiple matches would produce a sum of the market cap % instead of the first match).

**t1gereyez** · 05-31-2017, 04:00 PM

Originally Posted by 63falcondude

I don't understand the "moving values" part.

With an INDEX MATCH formula, the ranges in the formula change accordingly when you move the data around.

Will the formula update if the data in Sheets 2-3 are in a separate file? The intent is to leverage an existing file that contains the data and Sheet 1 will be in a separate excel file. Will change source on a monthly/quarterly basis.

**t1gereyez** · 05-31-2017, 04:03 PM

Originally Posted by mcmahobt

If the data range needs to be expanded, and data fields moved manually through a method such as copy > paste values, references to the correct field are then lost. This is what I interpreted as OP wrote, "...not tied to a column number as the data moves from column to column".

Will the formula update if the data in Sheets 2-3 are in a separate file and the columns move in the source file? The intent is to leverage an existing file that contains the data (sheets 2-3) and Sheet 1 will be in a separate excel file. Will change source on a monthly/quarterly basis. (I'm not sure that is even in English!)

**63falcondude** · 05-31-2017, 04:07 PM

Originally Posted by t1gereyez

Will the formula update if the data in Sheets 2-3 are in a separate file? The intent is to leverage an existing file that contains the data and Sheet 1 will be in a separate excel file. Will change source on a monthly/quarterly basis.

If the two workbooks are open then yes. I'm not sure if INDEX and MATCH work on closed workbooks.

I do know, however, that SUMPRODUCT works on closed workbooks.

If you do not have multiple matches, you could use a formula such as the one in post #7 (changing the whole-column references to something smaller).

**leelnich** · 05-31-2017, 05:02 PM

See post #17 for a much-improved version of this workbook. - LLN
Here's your workbook with ALL the formulae.

In F4: (F5 similar, varying only by which column contains values.)
=SUMPRODUCT((Sheet2!$A$2:$A$500=Sheet1!$A$2)*(Sheet2!$E$2:$E$500=Sheet1!$E4),Sheet2!$F$2:$F$500)

In E1: (E2,E3 similar, varying only by which column contains values.) (ARRAY FORMULA. Press CTRL+SHIFT+ENTER to confirm paste.)
=INDEX(IF(Sheet3!$A$2:$A$500=Sheet1!$A$2,Sheet3!$I$2:$I$500,""),ROWS(Sheet3!$A$2:$A2))

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

**leelnich** · 05-31-2017, 05:43 PM

Hey everybody, I haven't worked much with remote links, closed workbooks, and so forth. If someone else can help fill in those gaps, I'm sure the OP would appreciate it.

Thanks-Lee

**mcmahobt** · 06-01-2017, 08:22 AM

Originally Posted by t1gereyez

Will the formula update if the data in Sheets 2-3 are in a separate file and the columns move in the source file? The intent is to leverage an existing file that contains the data (sheets 2-3) and Sheet 1 will be in a separate excel file. Will change source on a monthly/quarterly basis. (I'm not sure that is even in English!)

This is a tricky question to answer.

1) Q. "Will the formula update if the data in Sheets 203 are in a separate file..."
A. The values will only update (or appear updated) when the external workbook is open within the same instance of Excel your workbook that houses the formula is in. Once the workbook is closed, the values will return an error (or blank if using IFERROR()).

2) Q. "...and the columns move in the source file?"
A. Array formulas (and even SUMPRODUCT) don't like using entire column/row references in their calculations. If you know that the data set in the external workbook isn't expected to shift more than a set number like 100 columns, then you can change the range of your formulas to capture that potential scenario. However, it's sounding more like you may want to go the path of VBA for this instead.

**t1gereyez** · 06-01-2017, 08:27 AM

Originally Posted by leelnich

Hey everybody, I haven't worked much with remote links, closed workbooks, and so forth. If someone else can help fill in those gaps, I'm sure the OP would appreciate it.

Thanks-Lee

Thanks all for your help and formulas! If anyone has any suggestions on how to best handle the any formula updates due to changes in the source file, I'd really appreciate your thoughts. The file I'd be linking to comes from another department so I wouldn't be aware of any column changes without reviewing it first (perhaps that is the way to go but hoping it can be more automated). Due to the exact column references in the formulas provided in this thread - the formulas do not update if another column is entered in the source file.

Again thanks to all!

**leelnich** · 06-01-2017, 06:01 PM

Originally Posted by t1gereyez

Due to the exact column references in the formulas provided in this thread - the formulas do not update if another column is entered in the source file.

Yes, I realized that after posting, it's been fixed in this version. I also implemented NAMED RANGES, which can be changed to refer to a DIFFERENT WORKBOOK. It seems to work, but as I said, I don't have much experience with external links. Good luck-Lee

New Formulae: in F4:

Formula:

Please Login or Register  to view this content.

In E11:

Formula:

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

LinkBack

Thread Tools

Rate This Thread

Display

NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Re: NEWBIE: Index, Match using vertical, horizontal and vertical with no column number AND

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] INDEX/MATCH based on 4 criteria, 2 vertical, 2 horizontal

index match two vertical one horizontal

[SOLVED] From Vertical to Horizontal / Match index?

to take info from sheet1 to sheet2 from horizontal to vertical using index match.

[SOLVED] Transposing data from vertical to horizontal (INDEX/MATCH)

Index Match with Horizontal and Vertical Matches

[SOLVED] Using Index & Match to search 2 vertical columns and return data to a vertical set

Bookmarks

Bookmarks

Posting Permissions