Complicated Index Match

[willia97]

I tried posting this problem on here last week and didn't get a valid solution so I figured I would try again.

I need to display the last text value in a column ignoring blank cells based on a different column. And to make it a little more complicated there are multiple columns which the text could appear in. I am struggling to accurately describe the formula I need, but I have attached an example of the problem.

The current formula I have finds the last text value in one column but if the last occurance of the person's name is next to a blank cell it displays a 0 instead of the last text value.

{=INDEX($D$3:$D$12,MATCH(MAX(IF(($C$3:$C$12=$K3),$B$3:$B$12,"")),$B$3:$B$12,0))}

Thank you.

[Pepe Le Mokko]

In L3 try

Please Login or Register  to view this content.

and pull down as needed

[willia97]

Pepe Le Mokko, that worked, but not quite how I want it to. This displays the value next to the last occurance of the name, but if it is blank I need it to still display the previous value. And also, if there are two values for different people on the same line it will display both values next to one person's name.

[Hyperdude]

hi willia,

I took your formula and did some modification. not sure if it is what you are looking for.

Please Login or Register  to view this content.

hope it helps.

[e.isayev]

=IF(ISBLANK(INDEX($D$3:$D$12,MATCH(1,($C$3:$C$12=I3)*(ISTEXT($D$3:$D$12)+ISTEXT($F$3:$F$12)),1))),INDEX($F$3:$F$12,MATCH(1,($C$3:$C$12=I3)*(ISTEXT($D$3:$D$12)+ISTEXT($F$3:$F$12)),1)),INDEX($D$3:$D$12,MATCH(1,($C$3:$C$12=I3)*(ISTEXT($D$3:$D$12)+ISTEXT($F$3:$F$12)),1)))

This formula does not show correct for Matt because its empty. Otherwise it would show the las entered text

[e.isayev]

ok
This array formula shows all correctly
=IFERROR(IF(ISBLANK(INDEX($D$3:$D$12,MATCH(MAX(($C$3:$C$12=I3)*B3:B12),B3:B12,0))),INDEX($F$3:$F$12,MATCH(MAX(($C$3:$C$12=I3)*B3:B12),B3:B12,0)),INDEX($D$3:$D$12,MATCH(MAX(($C$3:$C$12=I3)*B3:B12),B3:B12,0))),"")

[willia97]

Hyperdude, this is close to what I need. Thank you. The only issue is that if there is a blank for someone for one column it displays an error instead of the other column where there is an entry. And, I don't need both columns displayed just the most recent of the two. If both need to be displayed then that will work but if there is a way to only display the most recent between the two columns then that would be preferred.

I am seeing what I can figure out using what you have here and so far haven't figured out a way to fix the problem.

Thanks again.

[willia97]

eisayew, I also tried out yours but it doesn't seem to reference column E at all which causes some problems in the value it displays. What I am looking for is a formula which finds the person's name in C, then displays the last text value from column D adjacent to the person's name in C. Then look for the same person in column E, display the last text value from column F associated with that person. But then compare those two values and only display the one that occurred last. See the attachment for an example of what the desired results should be.

[willia97]

Hyperdude, by adding an IFERROR() to each of the INDEX() MATCH() sections I got it to display "None & Half" instead of the error. So by adding that in I can make it work I think. If you can think of a way to only display the last one of those two that would be perfect. Here is the formula I have now:

=CONCATENATE(IFERROR(INDEX($D$3:$D$12,MATCH(MAX(IF($C$3:$C$12=$K3,IF($D$3:$D$12="","",$B$3:$B$12),"")),$B$3:$B$12,0)),"None")," & ",IFERROR(INDEX($F$3:$F$12,MATCH(MAX(IF($E$3:$E$12=$K3,IF($F$3:$F$12="","",$B$3:$B$12),"")),$B$3:$B$12,0)),"None"))

Thank you again!

[willia97]

Figured it out! Thank you everyone for the help and guidance on how to accomplish this complex task!