Return multiple items with same vlookup value

[chander]

I am struggling with a spreadsheet wherein I need to populate mutliple outputs for a given match. I found the formula shown in the video appropriate and serve my need - BUT - when I am trying it in windows 7 enterprise, excel 2010 - it's showing "#value!" error...My output values are in text format instead of numbers (could that be the reason?).

Need help urgently.

Thanks in advance.

 

[NARAYANK991]

Hi Chander ,

Did you enter the formula as an array formula , using CTRL SHIFT ENTER ?

Can you post the formula you used , and mention how your data is laid out i.e. which cells / columns contain what ?

Narayan

 

[chander]

Hi Narayan. Appreciate your picking up my thread.

After posting the query here, I did some more googling and modified the formula as

=INDEX(Sheet1!$D$2:$E$1707;SMALL(IF(Sheet3!$A2=Sheet1!$D$2:$D$1707;ROW(Sheet1!$D$2:$D$1707)-MIN(ROW(Sheet1!$D$2:$D$1707))+1;"");COLUMN(Sheet1!E:E)))

I didnt do any ctrl shift enter... And, infact not able to relate to as to what exactly I should be doing other than normal formula entry in excel.

Attached is example of the problem I am trying to solve..

 

[Somendra Misra]

Hi Chander,

Try below array formula in B2 and copy across and down till H4.

=IFERROR(INDEX('base sheet'!$B$2:$B$10,SMALL(IF('summary sheet'!$A2='base sheet'!$A$2:$A$10,ROW('base sheet'!$A$2:$A$10)-ROW('base sheet'!$A$2)+1),COLUMNS($B2:B2))),"")

This is array formula, so must be entered with Ctrl+Shift+Enter.

Regards,

 

[chander]

Hi Somendra. Appreciate your sharing the specific formula.

I must admist, I am pretty basic with excel and not able to relate to the suggested approach of "entering with ctrl+shift+enter"...So, I enter the formula and then press ctrl+shift+enter OR something else..

Also, I entered the formula and it's working accurately for the first cell B2 - but not after that...pls find attached the amended spreadsheet.

Lastly conscious that I have shared only an example / dummy data. The actual database is different. And, I am using the example to learn the approach. So, appreciate your specific instructions / inputs so that I can use the same into other spreadsheet.

Thanks again in advance. This forum and experts like yourselves are quite a blessing for folks like myself.

Best. Chander

 

[NARAYANK991]

Hi Chander ,

Just as an option !

Narayan

 

[Somendra Misra]

Hi Chander,

See the attached file. You did not pressed Ctrl+Shift+Enter. The way to do it is like when you enter the formula in EDIT mode Press CTRL+SHIFT+ENTER. By entering with these keystoke you tell EXCEL that you want to do an array operation on functions which are in normal conditions don't do it.

In the above formula case, INDEX can handle array, SMALL can handle array, COLUMNS can handle array but IF is not able to handle array untill unless you tell EXCEL to do it. So, this keystroke is the key.

The proof that Excel had understood your command & requirement (and if all the calculation inside the formula goes well) than EXCEL will put {} around your formula, which can be only seen in formula bar. See the pic below. Dont try to manually insert these curly brackets and just Press ENTER as this as string.




You can also try @NARAYANK991 method of summary sheet. Vood Good way to visualise the summary.

Hope this will explain you the need of Ctrl+Shift+Enter, in case you need more clarification just write back and thanks for your kind words.

Regards,