• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

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
0
mcnuttlaw
Asked:
mcnuttlaw
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
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
 
krishnakrkcCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now