Vlookup returning multiple values for one value

[ensmith]

I am looking for a vlookup formula that will return multiple values that are in one column.

Sheet 1:
Cell O92 = Tech Name: Jerry S.

Sheet 2:
Column A = Tech Names (Jerry S.)

Column P = Fail Types

Jerry has three fail types associated with him in Column P

Tech Names Fail Type
Jerry S. Valid Regist.
Jerry S. Veh Reg.
Jerry S. Safety

How do i return all three fail types in a three separte cells on Sheet 1?




Thanks.

[oeldere]

You get better help if you post an Excel-example of your workbook, without confidential information.
Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.
Use BEFORE/AFTER sheets if that helps make it clearer.

[dilipandey]

Hi ensmith,

You can use the below formula:-

{=INDEX(Sheet2!$P$1:$P$4,SMALL(IF(Sheet2!$A$2:$A$4=Sheet1!$O$92,ROW(Sheet2!$A$2:$A$4),""),ROW(Sheet1!$A1)),1)}

See attached:- multiple value lookup.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[ensmith]

Thanks. I input the formula but I only was able to return two values. See attached.

[ensmith]

Thanks oeklere I think i got what i needed.

[dilipandey]

Hi ensmith,

I have updated the formula with correct range basis your sample data and included a error handler

{=IFERROR(INDEX(Sheet2!$B$1:$B4,SMALL(IF(Sheet2!$A$1:$A$4=Sheet1!$A$3,ROW(Sheet2!$A$1:$A$4),""),ROW(Sheet1!$A1)),1),"")}

See attached:- sample 2.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[ensmith]

thanks for your help.

[dilipandey]

Hi ensmith,

Would like to include the two red drop downs link to the name in A3

two red drop downs? do you wish to have only two values in the drop down?
where is the data source for this ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[oeldere]

I agree with DILIPandey, your question is not clear.

I suppose the answer should be the list below on the sheet JSO MTD Detail

Gerald O'Dwyer
NULL
Kyle Davenport

In that case you want an multiple linked dropdown:

Explaination on that item you find in the below links.


Create multiple linked dropdown list in excel

http://helpdeskgeek.com/office-tips/...ists-in-excel/
http://thefinch.wordpress.com/2008/0...datavalidatie/
http://www.contextures.com/xlDataVal02.html
http://www.snb-vba.eu/VBA_Afhankelijke_validatie.html also in Englisch

[ensmith]

Hello dilipandey,

Thanks I figured it out

Thanks

Regards,
DILIPandey

[ensmith]

Thanks for the links oeldere

[oeldere]

Glad I could help. Thanks for the reply.

[samp]

Hi ensmith,

I have updated the formula with correct range basis your sample data and included a error handler

{=IFERROR(INDEX(Sheet2!$B$1:$B4,SMALL(IF(Sheet2!$A$1:$A$4=Sheet1!$A$3,ROW(Sheet2!$A$1:$A$4),""),ROW(Sheet1!$A1)),1),"")}

See attached:- Attachment 168139

Regards,
DILIPandey

<click on below 'star' if this helps>

HI Dilipandey, hopefully you see this.
after much searching your answer is the closest to wha i am trying to do. Can this be done across multiple books? i am trying to use your formula and can't get it to work.
also maybe because i am trying to search entire columns? so instead of Sheet2!$B$1:$B4, i am tying to make it read Sheet2!B:B4,
will this not work?
thanks

[dilipandey]

Hi Samp,

welcome to the forum.

Suggest you to start your own thread and there you can mention about this thread as a reference... see the forum rules:-

http://www.excelforum.com/forum-rule...rum-rules.html

Regards,
DILIPandey
<click on below * if this helps>