Vlookup and multiple instances

[Searay]

I have a workbook containing various worksheets. One of these worksheet contains a table of expected results related to specific programs. I have created a template that enable the selection of one of the specific program which when selected have all related expected results automatically appears. I have entered the following formulas

=INDEX('LIST OF EXPECTED RESULTS'!$D$2:$D$51;MATCH($C$4;'LIST OF EXPECTED RESULTS'!$C$2:$C$51;0))

which works great for the first one, (and good for the second one if I switch the 0 for a 1) but not for the next which can be up to multiple instances. I have already tried the example provided by another user which consist of: =INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1))) (p.s. not the right info, only example) the control+shift+enter does not work as Excel return my formula has being erronous, therefore not allowing me to click ok.

Can someone help me resolve my dilema, please? Thank you for all your assistance.

Michelle

 

[T. Valko]

I have a workbook containing various worksheets. One of these worksheet contains a table of expected results related to specific programs. I have created a template that enable the selection of one of the specific program which when selected have all related expected results automatically appears. I have entered the following formulas

=INDEX('LIST OF EXPECTED RESULTS'!$D$2:$D$51;MATCH($C$4;'LIST OF EXPECTED RESULTS'!$C$2:$C$51;0))

which works great for the first one, (and good for the second one if I switch the 0 for a 1) but not for the next which can be up to multiple instances. I have already tried the example provided by another user which consist of: =INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1))) (p.s. not the right info, only example) the control+shift+enter does not work as Excel return my formula has being erronous, therefore not allowing me to click ok.

Can someone help me resolve my dilema, please? Thank you for all your assistance.

Michelle

There is an example here:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[Aladin Akyurek]

I have a workbook containing various worksheets. One of these worksheet contains a table of expected results related to specific programs. I have created a template that enable the selection of one of the specific program which when selected have all related expected results automatically appears. I have entered the following formulas

=INDEX('LIST OF EXPECTED RESULTS'!$D$2:$D$51;MATCH($C$4;'LIST OF EXPECTED RESULTS'!$C$2:$C$51;0))

which works great for the first one, (and good for the second one if I switch the 0 for a 1) but not for the next which can be up to multiple instances. I have already tried the example provided by another user which consist of: =INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1))) (p.s. not the right info, only example) the control+shift+enter does not work as Excel return my formula has being erronous, therefore not allowing me to click ok.

Can someone help me resolve my dilema, please? Thank you for all your assistance.

Michelle

C5, just enter:

=COUNTIF('LIST OF EXPECTED RESULTS'!$C$2:$C$51;C4)

C6, control+shift+enter, not just enter, and copy down:

=IF(ROWS($C$6:C6)<=$C$5;INDEX('LIST OF EXPECTED RESULTS'!$D$2:$D$51;
    SMALL(IF('LIST OF EXPECTED RESULTS'!$C$2:$C$51=$C$4;
      ROW('LIST OF EXPECTED RESULTS'!$C$2:$C$51)-ROW('LIST OF EXPECTED RESULTS'!$C$2)+1),
       ROWS($C$6:C6)));"")

 

[T. Valko]

I have a workbook containing various worksheets. One of these worksheet contains a table of expected results related to specific programs. I have created a template that enable the selection of one of the specific program which when selected have all related expected results automatically appears. I have entered the following formulas

=INDEX('LIST OF EXPECTED RESULTS'!$D$2:$D$51;MATCH($C$4;'LIST OF EXPECTED RESULTS'!$C$2:$C$51;0))

which works great for the first one, (and good for the second one if I switch the 0 for a 1) but not for the next which can be up to multiple instances. I have already tried the example provided by another user which consist of: =INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1))) (p.s. not the right info, only example) the control+shift+enter does not work as Excel return my formula has being erronous, therefore not allowing me to click ok.

Can someone help me resolve my dilema, please? Thank you for all your assistance.

Michelle

It looks like the problem with the example formula is that it uses commas as separators while your formula use semi-colons as separators.

Try replacing the commas with semi-colons. These separators are region specific.

Tip: if you index the entire column then you don't need to use an "offset correction".

Array entered**:

=INDEX(C:C;SMALL(IF(B$2:$B$6=B$8;ROW(B$2:B$6));ROWS(A$1:A1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Searay]

Thank you very much for your help. I did replace the coma with semi-colon and was able to use control-shift-enter with the formula that Aladin Akyurek provided(as my data was used, therefore much easier for me to understand). Wonderful!

My only dilema now is that the "Expected Results" are in a text format not in a numerical format. Thus, my question is: Is there a specific way to have it recognize the text as being first/last (compare to small/large), or should I add a column containing a row with a numerical value associated to the program then using it as the lookup value.

Please accept my apologies if you have difficulty understanding what I am trying to describe as I am French and it is sometimes a bit tricky.

Cheers!

Michelle

 

[Aladin Akyurek]

Thank you very much for your help. I did replace the coma with semi-colon and was able to use control-shift-enter with the formula that Aladin Akyurek provided(as my data was used, therefore much easier for me to understand). Wonderful!

That's great. If you want me to, I can provide the French versions of the formulas.

My only dilema now is that the "Expected Results" are in a text format not in a numerical format. Thus, my question is: Is there a specific way to have it recognize the text as being first/last (compare to small/large), or should I add a column containing a row with a numerical value associated to the program then using it as the lookup value.

Please accept my apologies if you have difficulty understanding what I am trying to describe as I am French and it is sometimes a bit tricky.

Cheers!

Michelle

Maybe I'm not understanding this. Are you referring to what these formulas return?

 

[T. Valko]

Thank you very much for your help. I did replace the coma with semi-colon and was able to use control-shift-enter with the formula that Aladin Akyurek provided(as my data was used, therefore much easier for me to understand). Wonderful!

Good deal. Thanks for the feedback!

My only dilema now is that the "Expected Results" are in a text format not in a numerical format. Thus, my question is: Is there a specific way to have it recognize the text as being first/last (compare to small/large), or should I add a column containing a row with a numerical value associated to the program then using it as the lookup value.

Please accept my apologies if you have difficulty understanding what I am trying to describe as I am French and it is sometimes a bit tricky.

Cheers!

Michelle

I'm not sure I understand what you're asking.

The SMALL function refers to the samllest row number where the data is located. So, the formula returns the data from the top of the range (the smallest row number) to the bottom of the range (the smallest N row number).

 

[Searay]

Ah! I see, I have completely misunderstood the formula. Which, confuse me now. My table that contains the program and expected results have let's say

12 programs and each program have 2 to 7 expected results like this example:

Program Expected Results
Name 1 Expected Results 1
Name 1 Expected Results 2
Name 2 Expected Results 1
Name 2 Expected Results 2
Name 2 Expected Results 3
etc.

All the Program Names are in alphabetical orders (in my table) and are listed as a Data Validation List in another worksheet entitled Template. In the Template I have multiple emptied fields called Expected Results which should be filled out when the client select the Program. I wanted to enter the formula in these emptied fields.

However, when entering the formula that you have provided (which btw extremelly appreciated) it gives me the first Expected Results of the table which is not necessarily associated with the selected program in the Template. I am certain that I am doing something wrong but don't seem to put my finger on it.

(P.S.: Thank you for offering the French version, but I think I will keep writing in english. Don't be shy to tell me if you do not understand me)

Merci pour tout. A bientôt.

Michelle

 

[T. Valko]

Ah! I see, I have completely misunderstood the formula. Which, confuse me now. My table that contains the program and expected results have let's say

12 programs and each program have 2 to 7 expected results like this example:

Program Expected Results
Name 1 Expected Results 1
Name 1 Expected Results 2
Name 2 Expected Results 1
Name 2 Expected Results 2
Name 2 Expected Results 3
etc.

All the Program Names are in alphabetical orders (in my table) and are listed as a Data Validation List in another worksheet entitled Template. In the Template I have multiple emptied fields called Expected Results which should be filled out when the client select the Program. I wanted to enter the formula in these emptied fields.

However, when entering the formula that you have provided (which btw extremelly appreciated) it gives me the first Expected Results of the table which is not necessarily associated with the selected program in the Template. I am certain that I am doing something wrong but don't seem to put my finger on it.

(P.S.: Thank you for offering the French version, but I think I will keep writing in english. Don't be shy to tell me if you do not understand me)

Merci pour tout. A bientôt.

Michelle

Here's a small sample file that demonstrates this.

zzzSearay.xls 15 kb

http://cjoint.com/?AFdcCP3mo2b

 

[Searay]

Thank you T. Valko for taking the time once again to assit me. I will use your sample to complete my work. Have an excellent evening!

Sincerely,

Michelle