Create list based on outcome of three columns

Using Excel 2003 I've run into a problem I cannot find the answer to (though there are so many posts here, I'm sure somebody has asked it.)

I've got two columns of data containing numbers (Column B & D). I'm using the following formula to match values in Column D to any of those in Column B (so all values in D are at some point in time compared to all values in B and if they match, a response is generated in Column F on the same row as the Column D value that was being matched against).

=IF(ISERROR(MATCH(D2,List1,0)), "No","Yes (" & MATCH(D2,List1,0)+1 & ")")

What I would like is to have a third list generated in Column H, which includes all values that positively matched, sorted into a contiguous list from smallest to largest.

For example:

B.........D......F...............H
1..........4......YES(4).......1
2..........7......NO.............4
3..........1......YES(1)
4..........9......NO


Is this even possible? I am fairly new to using Excel for this kind of thing. Any assistance, preferably with an explanation of why the formula works, would be awesome!

Yes it will be possible but I for one would be keen to know the size of List1 as this (IMO) will have a direct impact on the approach you should adopt.

I confess I'm also a little perplexed by your formula / suggested results - the values I would expect in the sample based on the formula are would be YES(5), NO, YES(2), NO ... ie you're adding 1 to the MATCH result (4,1 respectively).

NOTE: I have asked that your other duplicate thread be closed.

Originally Posted by Arnifix

The Match +1 bit simply allows for me to have a header row. Without the +1 all the row numbers for matching values are out by one and it makes it harder to find the value.

For the example I used above, remember that Row 1 contains B D F & H while Row 2 is the first to actually contain a value.

I'm afraid to me at least the above does not clear the confusion.

You suggested a formula for F2 of:

=IF(ISERROR(MATCH(D2,List1,0)), "No","Yes (" & MATCH(D2,List1,0)+1 & ")")

Given the sequential "1 to n" listing in List1 it follows that:

-- if List1 excludes header row then there would be no need to adjust the returned value at all.

-- if List1 includes header row then you would in fact need to subtract rather than add 1 from the MATCH result.

That is the confusion on my part regards existing formulae.


In relation to your actual question - assuming the values in List1 are unique (unknown):

... to avoid unnecessary calculations
I2: 
=COUNTIF(F:F,"YES*")

...to return unique results
H2: 
=IF(ROWS(H$2:H2)>$I$2,"",SMALL(IF(ISNUMBER(MATCH(List1,$D$2:$D$10000,0)),List1),ROWS(H$2:H2)))
confirmed with CTRL + SHIFT + ENTER
copied down as far as deemed necessary

NOTE the need for CTRL + SHIFT + ENTER for the unique listing formula - ie this is an Array formula - confirming with Enter alone will not suffice.