Lookup list for closest values to a dynamic list

[jayengee]

Hi all,

Sorry that I'm asking for help on my first post. I've been using this forum religiously for work, and it usually seems like my question has been asked before. However, I've come across a scenario that I am unsure how to tackle.

What I'm trying to do is essentially model a diagnostic test. A student gets a starting ability estimate, then a lookup formula looks in a separate sheet for an item with a certain ability level that is closest to the ability estimate, returning both the item name and the item difficulty. Another formula then re-estimates the student ability and the cycle continues.

My main issue is with the lookup formula. I tried to tackle this by using index, match, small, and abs functions:

=INDEX('NU items'!$B$2:$B$800,MATCH(SMALL(ABS('NU items'!$A$2:$A$800-'Simulation'!$L17),1),ABS('NU items'!$A$2:$A$800-'Simulation'!$L17),0))

Where B2:B800 is column for item names, A2:A800 is the column for item difficulty, and L17 is the ability estimate used, thus giving me the item name of the item with the smallest absolute difference from the ability estimate.

The problem is that I can't have duplicate items, which this may give (and does) if the student ability doesn't change much. What I then tried to do was to set up 3 more lookups with small(..., k) where k = 2, 3, 4, and had a countif formula to check if the first item had been used, then use item 2, then item 3, etc. However, when I have 2 items of the same difficulty, the k=2 formula gives me the same value as the k=1 formula.

Any ideas? Is there any way I can exclude a row from my array when looking up the items?

Much obliged!

 

[jayengee]

Hi again,

I've changed my formula to the following (inspired by class 785 by Mr Excel), and the evaluations seem to give me the right answer.


=INDEX('NU items'!$B$2:$B$715,MATCH(SMALL(IF('NU items'!$E$2:$E$715=0,IF(ABS('NU items'!$A$2:$A$715-L17)=SMALL(IF('NU items'!$E$2:$E$715=0,ABS('NU items'!$A$2:$A$715-L17)),1),'NU items'!$A$2:$A$715+ROW('NU items'!$A$2:$A$715)/100000)),1),'NU items'!$A$2:$A$715+ROW('NU items'!$A$2:$A$715)/100000))

I added in a column E in the data field that I'm pulling from, which is a countif for the number of times the item has been used. However, now I'm getting a circular reference warning and it returns a 0.

Help please!

 

[jayengee]

Not trying to bump this post, I promise. I just wanted to update the post so that any help would be relevant.

I allowed for circular references in options, and when I step through the formula via F9, the formula seems to be able to distinguish items that have been picked once already. However, even though using F9 gives me the return I expect, C+S+E on the formula gives me a duplicate item. What gives?

 

[Aladin Akyurek]

A scaled-down sample which demonstrates the problem you have might be helpful.

 

[jayengee]

A scaled-down sample which demonstrates the problem you have might be helpful.

Hi Aladin,

Thanks for the reply. Fortunately, I figured it out late last night.

The way I solved the issue I was having was to create a separate countif column for each successive item, counting the number of times the item had been displayed prior, rather than in total, thus solving the circular reference issue. Each lookup formula for each item then pointed to each separate countif column. I then used the trick in excellisfun lesson 758 of adding row()/100000000 to each value within the array formula to make each value distinct.

Final formula was:
=INDEX('NU items'!$B$2:$B$715,MATCH(SMALL(IF('NU items'!$F$2:$F$715=0,IF(ABS('NU items'!$A$2:$A$715-J19)=SMALL(IF('NU items'!$F$2:$F$715=0,ABS('NU items'!$A$2:$A$715-J19)),1),'NU items'!$A$2:$A$715+ROW('NU items'!$A$2:$A$715)/1000000000000)),1),'NU items'!$A$2:$A$715+ROW('NU items'!$A$2:$A$715)/1000000000000))

No duplicates, all values as close as they can be!