Thanks:  0
Likes:  0

What if your data in column A is text rather than numbers, this is giving me an error return when I try it
regards
Derek

2. OK, other solutions I know but this is how I approach these.

It needs a little knowledge of the data so is not entirely general but you can make it so. With the given example, where the numbers are small positive integers, it is relatively simple.

You have two ranges no doubt, call them numRange for the 20,39 etc. and textRange for the animals. The approach is to order them but introduce non-disturbing noise into that. So:

{=LARGE(numRange,{1;2;3;4;5;6})}

Gives you numbers, duplicates and all, but,

{=INDEX(TextRange,MATCH(LARGE(numRange+ROW(numRange)/100,{1;2;3;4;5;6}),numRange+ROW(numRange)/100,0))}

Does the text. By adding the 'ROW(numRange)/100' part you've simply broken up the equalities.

You can replace 100 by ROWS(numRange) if necessary and you can create a column of integers by {=ROW(OFFSET(INDIRECT("A1"),0,0,n))} for artitrary n.

3. On 2002-04-09 08:01, Derek wrote:
What if your data in column A is text rather than numbers, this is giving me an error return when I try it
regards
Derek
The target system of formulas applies to the text case too. It requires modification (indicated in bold below) at those places where the data type of A is exploited.

Consider

{"Field1","Field2";
"xza","cat";
"xza","cow";
"ret","rat";

in A1:B7.

In D1 enter:

=MATCH(REPT("z",20),Sheet1!A:A)-ROW(1:1)

In D2 enter: sad [ a lookup value ]

In E2 enter:

=IF(LEN(\$D\$2),VLOOKUP(\$D\$2,OFFSET(\$A\$2,0,0,\$D\$1,2),2,0),"")

In E3 enter and copy down:

=IF(AND(LEN(\$D\$2),COUNTIF(OFFSET(\$A\$2,0,0,\$D\$1,1),\$D\$2)>COUNTA(\$E\$2:E2)),INDEX(OFFSET(\$B\$2,MATCH(E2,OFFSET(\$B\$2,0,0,\$D\$1,1),0),0,\$D\$1,1),MATCH(\$D\$2,OFFSET(\$A\$2,MATCH(E2,OFFSET( \$B\$2,0,0,\$D\$1,1),0),0,\$D\$1,1),0)),"")

Here is what you get in the results area:

{6,"";
"","fox";
"","mouse"}

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•