Cascading Lookup Functions?

P

PeterB

I want to search for an item in one column (let's say Column D).
I then need to 'cascade that search' by searching from the row foun
with the first search to the end of the sheet's used area, but thi
time in Column A. Any ideas?

(The basic problem is that Column D has (let's say) Monday in 48 rows
Tuesday in 76 rows, Wednesday in 29 rows, etc. Column A has variou
numeric values and the values for the Mondays are the same as 48 of th
Tuesday values. I need to pick up the row number so I can compare th
value in one row with the corresponding value in another workbook.
 
C

CLR

You can use VLOOJUP to find multiple conditions by inserting a helper column
to the left of your table and CONCATINATING the values of interest into that
column and then doing the VLOOKUP that column.

Vaya con Dios,
Chuck, CABGx3
 
H

Herbert Seidenberg

Here is another way to find multiple conditions.
Assume your data looks like this:

List1 List2 List3 List4

CG Mon WN 2
EE Mon WN 3
BC Mon WN 1
AA Wed TN 1
EG Tue RN 2
CD Tue WN 1
AC Mon RN 2
FD Wed VN 5
EG Thrs UN 3
DB Mon WN 1

Result Look2 Look3 Look4
DB Mon WN 1
BC

The requirement is to find conditions in List2 thru List4 and
list all the matching items of List1.
R1C1 Reference Style
Select the headers List1 thru List4, the blank row and
the 10 data rows and
Insert > Name > Create > Top Row
Name cells Look2 thru Look4 in the same way.
Insert > Name > Define the following names
Nset Refers To =ROW(INDEX(C1,1):INDEX(C1,COUNTA(List1)+1))
FoundRow Refers To =(List2=Look2)*(List3=Look3)*(List4=Look4)*Nset
Fill in Look2 thru Look4 with the data you want to find in the lists.
In Results enter this array formula with Ctrl, Shift, Enter:
=INDEX(List1,IF(LARGE(FoundRow,Nset)=0,1,LARGE(FoundRow,Nset)))
In this example, Mon, WN and 1 are found twice and correspond to
DB and BC.
Options translates this into A1 Reference Style automatically.
 
P

PeterB

Thanks guys, I will try those out tomorrow (just looked at the page at
home and the worksheet is at work).
 
Top