Lookup

[niting]

I have a issue with a lookup function.


1st sheet:

Col A COl B

A 1

B 5

C 2

D 6

E 6


I want to pick up values if Col B value is greater than or equal to 5 and the corrosponding value against it in Col A. The data then I want is in sheet 2 with values B, D and E.


I used the following formula

IF(Sheet1!B2>=5,INDEX(Sheet1!$A$2:$A$10,MATCH(Sheet1!B2,Sheet1!$B$2:$B$10,0),1),FALSE)


and drag the formula. It gives me the values and "False" answers. However, for value 6, it only gives the first occurence i.e.D and repeats D in the column. It does not populate with other entry corrosponding to 6.


Could you please suggest correction. Thanks in advance.


Nitin

 

[Luke M]

Array formula:

=IF(COUNTIF(B:B,">=5")<ROWS($A$1:A1),"",INDEX(A:A,SMALL(IF($B$2:$B$10>=5,ROW($B$2:$B$10)),ROW(A1))))


Copy formula down as far as needed. Note that this formula will only display desired values, and will no show un-needed "FALSE" lines. Remember to confirm using Ctrl+Shift+Enter.

 

[niting]

MAN,


HOW??????I know u can anything with excel but u seriously make it believe.


Luke, could you please , please , explain the formula . I have to understand it and explain it to some 1 else(my boss).


Thanks

Nitin

 

[Luke M]

Sure thing Niting.


First part of formula is an IF function to see if we've displayed all the applicable records or not. The COUNTIF checks for how many records there are. This is compared to how many results/formulas have been dsplayed already (using the ROWS function). If all the records have been displayed, show "".


The latter half is the "meat" of the formula. We'll use the INDEX function to pull information from col A. But what row do we want? To figure this out, we need to know what all rows we care about. The inner IF formula:

IF($B$2:$B$10>=5,ROW($B$2:$B$10))

checks col B against your criteria. If it's true, return an array of the rows. So, this array will look something like:

{FALSE,3,FALSE,5,6}

The false's will be ingnore in the next function, our friend SMALL. We'll use the SMALL function to take the Nth smallest number. To increment N, we use the ROW(A1) (Using ROW is a common way to basically create a "counter" within a formula). So, the first formula says to take the 1st smallest number from the IF function (which will be 3). This goes into the INDEX function, saying to return the 3rd row from col A, giving the correct output. Next formula takes 2nd smallest number (which is 5), so we get info from the 5th row of col A.


Does that make sense?

 

[niting]

Luke ,


Makes sense totally. However, one doubt is there-:


the small function acts as the main operator. However, how does the formula works when the 3rd smallest number is repeated across the range and it also meets the criteria of >5. eg, 9 repeated across the range and diffrnt alphabets corrosponding to it. Why does not the formula only picks up the first value as wat happens with other formula. how is it able to return the corrrect figure??


Thanks

 

[Luke M]

Niting,

The trick is that the SMALL isn't looking at the values anymore (which yes, could have repeats) but at the rows (of which each are unique). So, if the "values" in B2:B6 are

1,9,9,2,9

The IF function converts that to:

FALSE,3,4,FALSE,6

Which tells the INDEX function to look at "rows" 3, 4, and 6