Reverse HLOOKUP

Hi there

I have a spreadsheet in which I need to perform a reverse HLOOKUP. The spreadsheet is attached for viewing.

I want a forumula in cell L2, which will look into cell K2 and if K2 is not empty, it will then look along the row to find the first cell that is not empty, then return the column name of that cell.

So in row two, if case closed is a yes, i want it to return the column ref that is not empty, ie if 1st meeting is full, and report written and 2nd meeting, but business written is empty, then it should return '2nd meeting'.

Any clues?
Thanks - Brokovich.

Hi,

=IF(K2<>"",IF(ISERROR(INDEX(A1:F1,1,MATCH("",A2:F2,-1))),INDEX(A1:F1,1,MATCH(1,A2:F2,-1)),INDEX(A1:F1,1,MATCH("",A2:F2,-1))),"")

Should do what you need, assuming that the "column names" are in row 1 and between columns A and F. Adjust the coloured ranges above as required.

Copy this code in a VBA module of your sheet, it will give you a user defined function.

Please Login or Register  to view this content.

The numbering of the columns starts in the first column of SearchRange.

Succes,

Erik

Feel free to change the code.