Complex lookup statement

[chloec]

Hi There, I have a complex lookup statement where I need to bring in complex data into another tab. It should function like this.


Look in some other tab and look at col W. Col W can have unlimited rows and a header row. The data consists of blank rows and some rows with a number in it. If Col W has a value above 0 in it and is not blank, then ixdex Col V (or take the cooresponding value from Col V). It would be GREAT if the results were sorted in order from largest to smallest.


Can you tell me what I am doing wrong with my ARRAY statement - this is working but NOT pulling in the correct datapoints. Why?


=IF(ROWS(A$37:A38)<= COUNTIF([WeeklyData.xls]qryBVFailandSCLX_Date!$W2:$W300,">0"), INDEX([WeeklyData.xls]qryBVFailandSCLX_Date!$V2:$V300, SMALL(IF([WeeklyData.xls]qryBVFailandSCLX_Date!$W2:$W300>1, ROW([WeeklyData.xls]qryBVFailandSCLX_Date!$W2:$W300)+1), ROWS(A$37:A38))),"")


For example: With the below example, I want the formula result to display (in the appropriate rows A1,A2,A3...)Lisa, Tom, Lisa, Stacy (in that order, from largest to smallest, if possible)

COL V / COL W

Lisa / 5

Lisa /

Lisa /

Tom / 5

Lisa /4

Tom /

Stacy /1

Sassy


Thanks!

 

[keymaster]

Interesting question. You need to deduplicate your data in column W first to get this work.


I say in column X, write something like this:

=W2+(0.0000000001)*row()


and fill it down.


And now use this in place of your array formula and drag it down.

Assuming you are writing this formula in A1


=INDEX(columnV, MATCH(LARGE(columnX, row()), columnX, 0))


row() portion:


If you are writing this formula not in A1, but say in A3 or F20, modify it like this:

row()-row($A$2 or $F$19)


That should work alright.


For more on deduplication & sorting data, refer to http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/

 

[NARAYANK991]

Hi Chloe ,


You can get the detailed explanation of the formula here :


http://www.get-digital-help.com/2009/04/16/create-unique-list-from-column-where-an-adjacent-column-meets-criteria/


Let us assume your data in column V is named List_category ; in the Refers To box , put in the address :


=[WeeklyData.xls]qryBVFailandSCLX_Date!$V2:$V300


Let us assume your data in column W is named List_value ; in the Refers To box , put in the address :


=[WeeklyData.xls]qryBVFailandSCLX_Date!$W2:$W300


Then put in the following formula in A2 , entering it as an array formula , using CTRL SHIFT ENTER :


=INDEX(List_category,MATCH(0,COUNTIF($A$1:A1,IF((List_value>0),List_category,"")),0))


Remember to either leave A1 blank , or ensure it contains data which does not match any of the entries in column V.


Narayan

 

[chloec]

This works GREAT! I went with Chandoo's solution! Thank you so much. I also utilized "ISERROR" function to get rid of any #NUM! errors. My final formula looks like this:


=IFERROR(INDEX([WeeklyData.xls]qryBVFailandSCLX_Date!$V:$V, MATCH(LARGE([WeeklyData.xls]qryBVFailandSCLX_Date!$W:$W, ROW()-ROW($A$46)), [WeeklyData.xls]qryBVFailandSCLX_Date!$W:$W, 0)),"")


You all are the BEST!