Solved

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

Posted on 2012-03-18
3
272 Views
Last Modified: 2012-03-19
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
Comment
Question by:mcnuttlaw
3 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 100 total points
ID: 37734692
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
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 400 total points
ID: 37734867
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
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37737735
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now