Adding unique items using an Array formula

[SHAWN]

Guys

Could really use some help here.

In Excel spreadsheet, I have two lists, looks something like that:


List 1 List 2
124 126
126 127
127 128
129 129
130
132

Now, List 1 is displayed in the report, List 2 is a lookup table.

The report looks like this:

List 1 Status
124
126 126
127 127
129 129

Required: Doing simple vlookup, find matching values from List 2.
Since List 2 has more items then List 2, all the missing values need
to be added to the Status field after all the List 1 values have been
looked up. So, List 1 will have blanks, and Status will have
values:128,130 and 132, something like this:

List 1 Status
.... ...
129 129
128
130
132

I know this could be done thru using an Array formula, I've seen an
example some years ago, however I can't find it.

I would really like to use the standard functionality and avoid VBA.

TIA.

 

[Harlan Grove]

Guys

In the gender-neutral sense?

In Excel spreadsheet, I have two lists, looks something like that:

List 1 List 2
124 126
126 127
127 128
129 129
130
132

Now, List 1 is displayed in the report, List 2 is a lookup table.

The report looks like this:

List 1 Status
124
126 126
127 127
129 129

Required: Doing simple vlookup, find matching values from List 2.
Since List 2 has more items then List 2, all the missing values need
to be added to the Status field after all the List 1 values have been
looked up. So, List 1 will have blanks, and Status will have
values:128,130 and 132, something like this:

List 1 Status
... ...
129 129
128
130
132

I know this could be done thru using an Array formula, I've seen an
example some years ago, however I can't find it.

I would really like to use the standard functionality and avoid VBA.

Assuming you don't need help bringing List 1 into the report, just the
values in the Status column. If the 123 cell under the List 1 column in the
resport were in cell A6 on some other worksheet, and Lists 1 and 2 were
named List1 and List2, respectively, and the Status columns were in column
B, try

B2 [*array* formula]:
=IF(AND(ISNUMBER(A6),A6<MIN(List2)),"",IF(COUNTIF(List2,A6),A6,
INDEX(List2,MATCH(0,COUNTIF(B$5:B5,List2),0))))

Select B2 and fill down as needed.

 

[Harlan Grove]

B2 [*array* formula]:
=IF(AND(ISNUMBER(A6),A6<MIN(List2)),"",IF(COUNTIF(List2,A6),A6,
INDEX(List2,MATCH(0,COUNTIF(B$5:B5,List2),0))))

So much for copy & paste from different ranges on my test worksheet.

Put that formula in cell B6 rather than B2.

 

[SHAWN]

Hi, Harlan

Thanks very much for your help, appreciate it.

Your formula worked perfect except for a case when Min value in List2
is less than in List1.

I couldn't get it to work. Could you please help.

Thanks

 

[SHAWN]

Harlan

Please disregard my previous post, the formula works just fine.

Thanks again, you helped me big time!!!