Lookup results

[cshipley]

Hi and Thanks everyone,

I am trying to create a formula that works similar to VLOOKUP except I want the result of the last match in the column. example:

A B
FTD1-1 NO
FTD1-2 YES
FTD1-3 NO
FTD1-5 NO
FTD1-4 YES
FTD1-1 YES
FTD1-6 YES
FTD1-7 NO
FTD1-8 YES
FTD1-3 YES

When I use VLOOKUP to match "FTD1-1" in column 'A', column 'B' returns "NO". What I need returned is the "YES" in column 'B' a few rows later.

I hope that makes sense.

Thanks

[BigBas]

Adjust your ranges to suit. In my example, you are looking up the Value that is entered into C1:

=INDEX($B$1:$B$7,LARGE(IF($A$1:$A$7=$C$1,ROW($A$1:$A$7)),1))

This is an ARRAY function, so you must press CTRL+SHIFT+ENTER to confirm

Let me know if it works

[cshipley]

Either I don't understand exactly what that formula is doing or I didn't do a very good job explaining my problem, so I will try to do a better job this time.
I am providing a sample file of what I am trying to do.

In column 'B' is a list of lessons that were worked on during different days. The lessons are not always completed on the first try. In column 'C' is a "YES" or a "NO" indicating if the lesson was completed on the day or not. In column 'E' is a list of the all lessons that need to be completed. I am trying to add a formula to column 'F', next to each lesson in column 'E', that retruns the current status of the lesson from column 'C'. That way at quick glance we can see which lessons have been completed and which lessons need more work.

The list that I am actually dealing with is potentially 180 rows involving well over 100 lessons for each student.

Thanks again for any help.

[BigBas]

In F3, enter this formula:

=INDEX($C$3:$C$16,LARGE(IF($B$3:$B$16=$E8,ROW($B$3:$B$16)-2),1))

Once entered, you must press CTRL+SHIFT+ENTER to confirm it. (you will notice brackets like these {} will enclose the formula automatically if done correctly.

Next, you can drag down as far as necessary.

Let me know if it works.

[cshipley]

Ok, again I'm sure it's something I'm doing wrong still, but after entering that formula it didn't seem to work correctly. This time I have attached the actual file I have been working with. The column that I am trying to enter it in is 'BU'. I left the formula the way I entered it in that column so hopefully it will be easy to see what I am trying to do. It looks like I will also need to add a function (ISERROR) to it to eliminate the errors on lessons that have not been worked on yet.

Thanks again for your help BigBas

[BigBas]

I'll try to walk you through the changes that I have made to the original formula, but forgive me if I have forgotten any, as I was making changes without thinking.

1. I changed your index start range from AI6 to AI7, since AI6 contains label headers.
2. At the end of the large formula we had -2. That has been changed to -6. That number represents the row that your information starts on. (So if you are on row 7, and you subtract 6, it treats row 7 as ROW #1 for the array.)
3. I enclosed it within an ISERROR clause. It checks that there is the Code in BT matches a code in AH, otherwise it leaves it leaves it blank.

New formula:

=IF(ISERROR(MATCH(BT9,AH7:AH206,0)),"",INDEX($AI$7:$AI$206,LARGE(IF($AH$7:$AH$206=$BT9,ROW($AH$7:$AH$206)-6),1)))

Confirmed with CTRL+SHIFT+Enter

Let me know if you have any further questions.