INDEX MATCH Function

[Jancysse]

Hi all, I'm trying (very hard) to populate a table based on the result in another cell. Have used Index Match and Lookup, but its starting to get a bit beyond me. Per table below, I am able to pull the comments in column E down into the "Work Required" Table and eliminate any gaps (using INDEX Function). What I can not seem to do do is get the check marks in the Poor, Avg and Excl columns from the top table to self populate in the 2nd table based on the input in Column A9 (pulled from Table 1) and below. Any help greatly appreciated.

TABLE 1
Category	Poor	Avg	Excl	Comment
Windows	X			Replace windows
Doors		X		Paint Door
Lawns			X
Gardens		X		Tidy Garden
Table 2
Work Required	Poor	Avg	Excl
Replace windows
Paint Door
Tidy Garden

<tbody>
</tbody>

 

[Andrew Poulsom]

Welcome to MrExcel.

In B9 copied down and across:

=T(INDEX(B$3:B$6,MATCH($A9,$E$3:$E$6,FALSE)))

 

[Jancysse]

Hi Andrew, Thanks for this! You're a star!!! Your solution works on my sample spread sheet perfectly, but I seem to be getting a #N/A when I put it into my working doc. I'm using named ranges in that. Would that impact it? Cant figure out what I'm doing differently ;(

 

[Jancysse]

Checked again...named ranges seems to be the problem...any thoughts on how I get around that?

 

[Andrew Poulsom]

What do the names refer to and what formula are you using?

 

[Jancysse]

The named range is E2:E6. I named it so I could use the index function to pull the Comments in E2:E6 into A9:A12 while eliminated blank lines...

 

[Andrew Poulsom]

And what formula are you using?