Vlookup Excel Help

Your red cells are currently empty, it would be worth seeing what should be in those cells so that it can be used to determine whether the column header is to be included.

So you want to return the header from the column where the cell is red? A lookup function only looks in one row or column. I think you might need to use a search.

Hi Chris,

Check in attached, do you want this way?
ChrisPike_Vlookup.xlsx

Shums - I think you have it the wrong way round. I believe Chris wants to create the list in range N6:O15 based on the contents of the red cells.

Rob,

You are right!

But requirement from Chris is still not cleared.

Hi Chris,

If you are open to a VBA solution, please place the following code on Sheet1 Module.
I have inserted a data validation list in M6 based on names in column A on Sheet2 with the help of a dynamic named range.

As per the code, once you select a name from the drop down list in M6, the code will place the all the header for if the corresponding cells in the row of the selected name are red.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub

Dim rngName As Range
Dim lc      As Long
Dim r       As Long
Dim i       As Long
Dim j       As Long
Dim x       As Variant
Dim y()     As Variant
Dim Rng     As Range
Dim Cel     As Range

lc = Range("B1").End(xlToRight).Column
x = Range("B1", Cells(1, lc)).Value
ReDim y(1 To UBound(x, 2), 1 To 2)

On Error GoTo Skip

If Target.Address(0, 0) = "M6" Then
    Application.EnableEvents = False
    Range("N5").CurrentRegion.Offset(1, 1).ClearContents
    If Target <> "" Then
        Set rngName = Range("A:A").Find(what:=Target.Value, lookat:=xlWhole)
        If Not rngName Is Nothing Then
            Set Rng = rngName.Offset(0, 1).Resize(1, UBound(x, 2))
            For Each Cel In Rng
                i = i + 1
                If Cel.Interior.Color = vbRed Or Cel.DisplayFormat.Interior.Color = vbRed Then
                    j = j + 1
                    y(j, 1) = j
                    y(j, 2) = x(1, i)
                End If
            Next Cel
            If j > 0 Then Range("N6").Resize(j, 2).Value = y
        End If
    End If
End If
Skip:
Application.EnableEvents = True
End Sub

Select allOpen in new window

ColumnHeaders.xlsm

Neeraj,
Your code looks good, but I need to make it easy to put into my sheet.
My sheet has many many columns and I am having trouble to put into a sheet with over 60 columns.

Also will this work with all my cells having this formula in each and every cell.

=IF(ISNUMBER(MATCH($C11&AT$1&AT$2,INDEX('Z:\Delta HSE\Safety Training\2019 Training\[Book1.xlsx]User_Status_Report'!$A$1:$A$19962&'Z:\Delta HSE\Safety Training\2019 Training\[Book1.xlsx]User_Status_Report'!$F$1:$F$22701&'Z:\Delta HSE\Safety Training\2019 Training\[Book1.xlsx]User_Status_Report'!$J$1:$J$22646,0),0)),"Done","")

Wow I know it's big
I don't really want to change it because it is working.

Thanks
Chris

Hi Rob, here is  the code in every cell.
I am pulling data from a closed workbook. It is looking for employee name and course name. The orginal refresh sheet where the data lives has maybe 20 columns or something and maybe 2000 rows.

=IF(ISNUMBER(MATCH($C11&AT$1&AT$2,INDEX('Z:\Delta HSE\Safety Training\2019 Training\[Book1.xlsx]User_Status_Report'!$A$1:$A$19962&'Z:\Delta HSE\Safety Training\2019 Training\[Book1.xlsx]User_Status_Report'!$F$1:$F$22701&'Z:\Delta HSE\Safety Training\2019 Training\[Book1.xlsx]User_Status_Report'!$J$1:$J$22646,0),0)),"Done","")

Thoughts?

Are the cells being coloured red by Conditional Formatting? If so I don't think VBA code can determine the colour in the normal way, vaguely recall it proving problematic.

Based on the formula above, I assume the contents of the cells are either blank "" or "Done". Which is it that gets turned red?

Ok Rob.....
I figured it out. We need to change the condition from a colored cell to a cell with done.

So when the cell has "Done" then pull the column header for the list.
Does that make sense? The person using the sheet is now changing the color manually, which is fine., so lets ignore the cell color.
Done is coming from another sheet.

Thanks
Chris

I thought about doing a pivot table, but it would just get too messy, as there are too many breaks in the names down the side, because of separation for the three shifts

See attached.

I have manually entered "Done" into those cells that were highlighted red and have applied Conditional Formatting (CF) on that range, CF rule is Cell = "Done" gives Red fill.

There is then a list of formulas in columns U & V which gives a column number (column U) up to the number of entries in row 1 and the result of a vlookup for that column offset in the grid (column V).

In column O there is then a TEXTJOIN function that concatenates the results of column V with a Carriage Return (CHAR(10)) between each entry and ignores blank fields.

That cell is then formatted to Wrap text so that the Carriage Return takes effect on the result.

To expand this to the real scenario, the ranges referred to in the various formulas will need to be expanded.
ChrisPike_Vlookup.xlsx

Hmmmm,
Rob we are getting closer. Please see attached sheet#2.
I think this will make it super clear.
I only need a list on NOT "DONE" trainings, not worried about color at all.

Have a look, I explain it further
Thanks so much.
Copy-of-ChrisPike_Vlookup_1.xlsx

So basically the other way round to what I suggested; I did ask which value went red, blank or Done, in an earlier comment but heh ho.

Amended version attached, it is just the formulas in column V that have changed, the IF statement looks for blank result in the VLOOKUP rather than Done.

If this table is formula driven and comes from a different sheet/file, it might be possible to do something better on the source data rather than using this matrix as an interim step
Copy-of-ChrisPike_Vlookup_1.xlsx

Hi Rob,
The source data is a refresh sheet that is downloaded on demand from a training software. So when we refresh all data on that sheet, it connects to the web and pulls data into it. Then this tracking sheet does a vlookup to look at 2 columns (trainings) and employee name (A) and puts a DONE into the cell if the name and training is on the list. I will fiddle with this one and see if I can get it to work on my working sheet.
As a side note, It would be ideally better if the list that gets pulled would appear into cells instead of all in one cell which changes the height of the entire row.

Thanks Rob will let me know. If I need to open a second question to get a tweak let me know.
Thanks
Chris

Hi Rob, I am still trying to make this work on my sheet,
Maybe there is a hidden formula. I am copying each formula one by one.
Working on it. Thanks

Chris

Thanks Guys, I figured it out for the most part.
Rob, I may hit you up again to help me put this into my actual workbook.
I am having a bit of difficulty, but your example works great.....
Chris