VLookUp Two Possible Values

[jnymris]

I've currently been using VLOOKUP perfectly fine untill i've encounted a problem with my application.

from the table i am currently looking up Their are two possible outcomes for SOME of my values i am looking up.

For exmaple if i look up 111111 their is the possability of one result
however if i look up 222222 their could be two result either 222222 or 222222.22
What i'm trying to do is create an if statement with VLOOKUP to try and define if this has two possible result and if so,show both result
My current plan is to use If(COUNTIF(B1:B200,"*"&A1&"*")>1," Two Values",VLOOKUP(A1,'DataSheet'!B2:D7,3,false))

which based of my current VLOOKUP should display the existing data if their is only one result HOWEVER if their is two should show Two Values, instead of showing "Two Values" is it possible to put "Value1:"&Value1&"\nValue2"&Value2

Thanks Very Much !

Johnny

 

[Yahya]

see if this will help
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ID</td><td style=";">Value</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">LookupValue</td><td style="text-align: right;;">111111</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">111111</td><td style="text-align: right;;">22222</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">111111</td><td style="text-align: right;;">22222.22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">22222</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">111112</td><td style="text-align: right;;">3333</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">22222.22</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$F$3:F3</font>)<=COUNTIF(<font color="Red">$A$2:$A$4,$F$1</font>),INDEX(<font color="Red">$B$2:$B$4,SMALL(<font color="Green">IF(<font color="Purple">$A$2:$A$4=$F$1,ROW(<font color="Teal">$A$2:$A$4</font>)-ROW(<font color="Teal">$A$2</font>)+1</font>),ROW(<font color="Purple">1:1</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[jnymris]

see if this will help
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ID</td><td style=";">Value</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">LookupValue</td><td style="text-align: right;;">111111</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">111111</td><td style="text-align: right;;">22222</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">111111</td><td style="text-align: right;;">22222.22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">22222</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">111112</td><td style="text-align: right;;">3333</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">22222.22</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$F$3:F3</font>)<=COUNTIF(<font color="Red">$A$2:$A$4,$F$1</font>),INDEX(<font color="Red">$B$2:$B$4,SMALL(<font color="Green">IF(<font color="Purple">$A$2:$A$4=$F$1,ROW(<font color="Teal">$A$2:$A$4</font>)-ROW(<font color="Teal">$A$2</font>)+1</font>),ROW(<font color="Purple">1:1</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

thank you very much for getting back to me, this is what i'm trying to achieve.

ID Value
1 0141
1 0131
2 0151

When Look Up Value = 1
i want to display
0141 & 0131

When Look Up Value = 2 i want to show 0151
This is my current code

=IF(COUNTIF(A2:B4,F1)>1,"Two Values",VLOOKUP(F1,A2:B4,2,FALSE))

BTW how do you get the nice fancy wee tables and stuff?

 

[jnymris]

Anyone?

 

[Yahya]

try this UDF

Function LOOKUPIT(a As Range, Rng As Range)
Dim c As Range
For Each c In Rng
    If c.Value = a.Value Then
        LOOKUPIT = c.Offset(, 1).Value & "," & LOOKUPIT
    End If
Next c
LOOKUPIT = Left(LOOKUPIT, Len(LOOKUPIT) - 1)
End Function

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ID</td><td style=";">Value</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">LookUp Value</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0141</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0131</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0131,0141</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0151</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=LOOKUPIT(<font color="Blue">F1,A2:A4</font>)</td></tr></tbody></table></td></tr></table><br />