INDEX MATCH Function

Jancysse

New Member
Joined
Oct 1, 2014
Messages
4
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
CategoryPoorAvgExclComment
WindowsXReplace windows
DoorsXPaint Door
LawnsX
GardensXTidy Garden
Table 2
Work RequiredPoorAvgExcl
Replace windows
Paint Door
Tidy Garden

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

In B9 copied down and across:

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

Jancysse

New Member
Joined
Oct 1, 2014
Messages
4
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

New Member
Joined
Oct 1, 2014
Messages
4
Checked again...named ranges seems to be the problem...any thoughts on how I get around that?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Jancysse

New Member
Joined
Oct 1, 2014
Messages
4
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,109,159
Messages
5,527,149
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top