Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup functions | Excel Worksheet Functions | |||
Allow refence in the "table_array" position of Lookup functions | Excel Worksheet Functions | |||
Lookup Functions | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
Lookup Functions | Excel Worksheet Functions |