Excel Index to look up multiple values

Question

I have a small data set of 2 columns and several rows (columns A and B)

I want to return each instance of codeblk 3 in a formula that is elsewhere in my sheet, (so a vlookup is out as it only shows the first instance) if it does not appear then a message to say its not there should come up.

I have the formula partially working but i cant see the reason why its not displaying the values.

My formula is as below: This is an array

{=IF(ISERROR(INDEX($A$55:$B$70,SMALL(IF($B$55:$B$70=3,ROW($B$55:$B$70)),ROW(1:1))-1,1)),"No value's produced",INDEX($A$2:$C$7,SMALL(IF($B$55:$B$70=3,ROW($B$55:$B$70)),ROW(1:1))-1,1))}

The result that shows up is only "No values produced" but it should reflect statement B, C and D in 3 separate cells (when changing ROW(1:1), ROW(2:2) etc)

{=SMALL(IF($B$56:$B$69=4,ROW($B$56:$B$69)),ROW(1:1))} - This produces the result 68 which is the correct row.

Any ideas? Thanks,

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

This is an array formula - Validate the formula with Ctrl+Shift+Enter while still in the formula bar

=IFERROR(INDEX($A$55:$B$70,SMALL(IF($B$55:$B$70=3,ROW($B$55:$B$70)-54),ROW(1:1)),1),"No value's produced")

The issue you are facing is that your index starts it's first row on $B$55, you need to offset the row numbers in the array to reflect this. For example, the INDEX contains 16 rows but if you had a match on the first row you are asking for the 55th row from that INDEX(), it just can't fulfil that.

EDIT

The offset was out of sync as your original formula included another -1 outside of the IF(), I also left an additional bracket in play (the formula above has now been edited)

The ROW() function will essentially translate $B$55:$B$70 into ROW(55:70) which will produce the array {55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70} so the offset is needed to translate those row numbers in to the position they represent in the indexed data of INDEX().

The other IF() statement then produces and array of {FALSE;2;3;4;FALSE etc.

You can see these results by highlighting parts of the formula in the formula bar and hitting F9 to calculate.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 5, 2018 at 11:37

Glitch_Doctor

3,0443 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FuriousD Over a year ago

So i tried using the version you stated but it did not work. As my understanding is the index function would use the small formula to identify the row number within the range i specified in the index (A55-B70) - so effectively adding the -54 made it error as it was looking for a negative row number. My understanding may be incorrect though

FuriousD Over a year ago

Richard, thank you that worked - and more importantly i understand why. Thank you so much. Genius!!!

Collectives™ on Stack Overflow

Excel Index to look up multiple values

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related