Search for a value in an array with multiple rows and columns

I need to search for a value in an array that has multiple rows and columns and return the value in the corresponding column/row.

In this screenshot,

Array would be the cells darkly shaded (C5:T8)
Return values in column B (B5:B8)

Search for 1 should return Course 2
Search for 2 should return Course 3

sample
LVL 2
mcnuttlawAsked:
Who is Participating?
 
krishnakrkcConnect With a Mentor Commented:
Hi,

=IF(COUNTIF(C5:T8,C2),INDEX(B5:B8,MIN(IF(C5:T8=C2,ROW(B5:B8)-ROW(B5)+1))),"")

where C2 holds the number. It's an array formula. Conformed with CTRL + SHIFT + ENTER

Kris
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Paste the given function in a normal module and then use the formula

=alookup(2,B4:Q11)

Function alookup(r, ar As Range)
alookup = Cells(ar.Offset(1, 1).Find(r).Row, ar.Column)
End Function

See attached file
Alookup.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.