Create list based on outcome of three columns

[Arnifix]

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!

[DonkeyOte]

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.

[Arnifix]

I made a duplicate thread? Sorry!

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.

The two lists that go in Columns B & D will vary in length between about 50 values and 10000 values, as the intended use is just to identify matches I'm trying to make it general purpose rather than specific.

[DonkeyOte]

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:

Please Login or Register  to view this content.

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):

Please Login or Register  to view this content.

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.