Of LOOKUP, MATCH, INDIRECT, and more?

  • Thread starter Thread starter SCW
  • Start date Start date
S

SCW

Reposted from TIP area (I hope this is the correct forum).
Any assistance would be great...
I have sheet that looks like this:

.. A ..... B ..... C ........... D ..... E ..... F .............. G
..... H .... I
1 RANK STAT DESC ..... RANK STAT DESC ........ RANK STAT DESC
2 ....50 .....D My desc 1 ..125 .....R My desc 20
3 ........ .....A My desc 2 ..500 .....D My desc 21
4 ....70 .....D My desc 3 ..300 ....... My desc 22
5 ....20 .....R My desc 4
6

Then from these multiple RANK/priority columns, I pick the 4 highest
priorities(using the "LARGE" function) and show those numbers in 4 rows
(500, 300,125,70)...
but now, how do I show the corresponding STATUS and DESCRIPTION
column/rows?
???

Lookup and Vlookup, appear to be able to only handle a single column,
where my sheet (as shown above) has multiple repeating columns. Does
MATCH and/or INDIRECT play into this at all?

Unfortunately, this sheet/report must be oriented in a "landscape"
manor, necessitating these multiple/similar columns.

Any ideas or help is greatly appreciated!
Thx in advance,
Signed,
Curious g
 
I'm getting close...
From the top 4 priorities/RANK list, I used this:

=OFFSET(B12,MATCH(Z3,B13:B77,0),2)

But this only works for a single column of data to pull from (B13:B77),
but if I have mulitple columns of data then it throws the "#N/A"
response... as in this formula produces:

=OFFSET(B12,MATCH(Z3,($B$13:$B$78,$N$13:$N$78,$F$13:$F$78,$J$13:$J$78,$R$13:$R$78,$V$13:$V$78,$Z$13:$Z$78),0),2)

Is there a work-around for multiple columns of data to search?
I can email the workbook if this helps at all.
 
Back
Top