Double Match and Index

[gaudrco]

I have a formula that uses a match and index function to display the correct data. I have a dropdown list of values in cell ("B3"). The formula finds the matching value of cell ("B3") on sheet ("Competitor Comparison Data") which allows me display different sets of data, depending on what value I choose in the list of values in cell ("B3").

My current formula is this:
=IFERROR(INDEX('Competitor Comparison Data'!H:H,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")

I'm trying to have this formula adjusted so that the values in Row 7 on the current sheet matches the values in Row 5 on Sheet ("Competitor Comparison Data"), then take the values of that column in Sheet ("Competitor Comparison Data").

The purpose is this: I have a listbox of the values in Row 7. These values are competitor names. The listbox allows for a user to rearrange the list items. When the use clicks the "Close" button, the new order of the listbox items becomes the new column headers in Row 7. So the index and match formula will be able to see that the column header is now a different competitor name and the data will change to match to the corresponding data.

Any ideas?

 

[NoSparks]

syntax of INDEX function:
INDEX(array, row_num, [column_num])

Your existing formula has no column number because there is no choice, the array is a single column.

Change the array part from being single column to being your used range
'Competitor Comparison Data'!$A$1:$EA$2453

You already have your row requirement

For the column number, match the row 7 value to the row 5 value of 'Competitor Comparison Data
'MATCH(D$7,'Competitor Comparison Data'!$A$5:$AW$5,0)

$EA$2453 and $AW$5 come from the file you previously provided.

 

[gaudrco]

Sorry I have been out for a few days.

I adjusted my formula to your suggestion but I am getting "No Match"

This is how the new formula looks right now:
=IFERROR(INDEX('Competitor Comparison Data'!$A$1:$EA$2453,MATCH(D$7,'Competitor Comparison Data'!$A$5:$AW$5,0)+ROWS($A$8:$A8)-1),"No Match")

 

[gaudrco]

Okay I think I figured out the formula but I can't get it so that when I use the fill tool, the formula adjusts for the other cells in the table. This is the formula:
=IFERROR(INDEX('Competitor Comparison Data'!$A$1:$EA$2453,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0),MATCH(D$7,'Competitor Comparison Data'!$A$5:$AW$5,0)+ROWS($A$8:$A8)-1),"No Match")

So when I click this cell and pull the fill tool down to fill the whole table, it does not adjust the formula to match for the next cell down. Maybe I have the formula wrong?

 

[NoSparks]

Why did you move the row adjustment from the row to the column portion of the formula ?

 

[gaudrco]

I was just trying out to see what would work. I removed the +ROWS($A$8:$A8)-1) all together and it seems to be working still. I dont know why. This is what the formula looks like going down the a column:
=IFERROR(INDEX('Competitor Comparison Data'!$A1:$EA$2453,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0),MATCH(D$7,'Competitor Comparison Data'!$A$5:$AW$5,0)),"No Match")
=IFERROR(INDEX('Competitor Comparison Data'!$A2:$EA$2453,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0),MATCH(D$7,'Competitor Comparison Data'!$A$5:$AW$5,0)),"No Match")
=IFERROR(INDEX('Competitor Comparison Data'!$A3:$EA$2453,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0),MATCH(D$7,'Competitor Comparison Data'!$A$5:$AW$5,0)),"No Match")

And this is what it looks like going across a row:
=IFERROR(INDEX('Competitor Comparison Data'!$A1:$EA$2453,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0),MATCH(D$7,'Competitor Comparison Data'!$A$5:$AW$5,0)),"No Match")
=IFERROR(INDEX('Competitor Comparison Data'!$A1:$EA$2453,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0),MATCH(E$7,'Competitor Comparison Data'!$A$5:$AW$5,0)),"No Match")
=IFERROR(INDEX('Competitor Comparison Data'!$A1:$EA$2453,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0),MATCH(F$7,'Competitor Comparison Data'!$A$5:$AW$5,0)),"No Match")

 

[NoSparks]

Show me the formula you put into D8

 

[gaudrco]

Show me the formula you put into D8

=IFERROR(INDEX('Competitor Comparison Data'!$A1:$EA$2453,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0),MATCH(D$7,'Competitor Comparison Data'!$A$5:$AW$5,0)),"No Match")

 

[NoSparks]

refer back to post 2

 

[gaudrco]

I have it working now thank you