Cascading Lookup Functions?
 

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 found
with the first search to the end of the sheet's used area, but this
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 various
numeric values and the values for the Mondays are the same as 48 of the
Tuesday values. I need to pick up the row number so I can compare the
value in one row with the corresponding value in another workbook.)


--
PeterB
------------------------------------------------------------------------
PeterB's Profile: http://www.excelforum.com/member.php...o&userid=20288
View this thread: http://www.excelforum.com/showthread...hreadid=494595

Cascading Lookup Functions?
 

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



"PeterB" wrote:


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 found
with the first search to the end of the sheet's used area, but this
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 various
numeric values and the values for the Mondays are the same as 48 of the
Tuesday values. I need to pick up the row number so I can compare the
value in one row with the corresponding value in another workbook.)


--
PeterB
------------------------------------------------------------------------
PeterB's Profile: http://www.excelforum.com/member.php...o&userid=20288
View this thread: http://www.excelforum.com/showthread...hreadid=494595

Cascading Lookup Functions?
 

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(Fou ndRow,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.

Cascading Lookup Functions?
 

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


--
PeterB
------------------------------------------------------------------------
PeterB's Profile: http://www.excelforum.com/member.php...o&userid=20288
View this thread: http://www.excelforum.com/showthread...hreadid=494595