Getting multiple values from vlookup

[inforave]

Good day to all,

I' ve been scouring the Internet for a way to get vlookup to return not just the first value meeting the criteria but all of them but so far no luck.

PR1 100
PR2 300
PR3 500
PR4 700
PR1 200
PR4 800
PR3 600
PR2 400

PR1 Mutiple Values

To make my question clearer, lets say we have the above data in columns A and B. The lookup table is A1:B8 and the lookup value is in cell A10. Is there a way to get vlookup to return all the values from column B, matching the criteria (i.e. the lookup value in cell A10) and show the results in concatenated form in cell B10; In this simple example it should return 100,200.

I know this could be done using VBscript but I wonder if there is an easier way, using the available functions in Excel 2007.

I thought of one way it could be done but don't know how to express it in Excel language.

1. count the number of rows in the lookup range.

2. find the row number of the first occurence.

3. display the first occurence

4. search for the next occurence in a new range, defined as (A1+(row number of the first occurrence)):B8. For example if the first occurence was found in row 3, the new range would be defined as (A1+3:B8), i.e. A4:B8.

5. Repeat the process until we get the last value.

Any help would be greatly appreciated!!!

 

[Sandeep Warrier]

Hi & welcome to the Board!

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;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="font-weight: bold;;">Data</td><td style="font-weight: bold;;">Data</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Condition</td><td style="font-weight: bold;;">Data</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">PR1</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">PR1</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">PR2</td><td style="text-align: right;;">300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">200</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">PR3</td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">PR4</td><td style="text-align: right;;">700</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">PR1</td><td style="text-align: right;;">200</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;">7</td><td style=";">PR4</td><td style="text-align: right;;">800</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;">8</td><td style=";">PR3</td><td style="text-align: right;;">600</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;">9</td><td style=";">PR2</td><td style="text-align: right;;">400</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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;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>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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">F2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$2:$B$9,SMALL(<font color="Green">IF(<font color="Purple">$A$2:$A$9=$E$2,ROW(<font color="Teal">$A$2:$A$9</font>)-ROW(<font color="Teal">$A$2</font>)+1</font>),ROWS(<font color="Purple">$F$1:F1</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 />

 

[inforave]

A million thanks! I'll try it immediately!

 

[Akashwani]

Hi,

Welcome to MrExcel.

This probably isn't the best solution, it will not be if you have a large data range.
But you could look at trying something like this.....

Excel Workbook
	A	B	C	D	E	F	G
1	PR	Value	*	PR	Count	*	Result
2	PR1	100	*	PR1	2	*	100,200
3	PR2	300	*	*	*	*	*
4	PR3	500	*	Values	*	Values	*
5	PR4	700	*	100	*	100	200
6	PR1	200	*	200	*	*	*
7	PR4	800	*	*	*	*	*
8	PR3	600	*	*	*	*	*
9	PR2	400	*	*	*	*	*
Sheet1

The formula in D5 needs to be copied down.
The formula in F5 needs to be copied across.
You can use either of these formulas to return all matches.

The formula in G2 for the result would be MASSIVE if you have a large data and not practical.

I hope this gives you some ideas if it isn't a suitable solution.

Good look.

Ak

I'm unavailable for further comment today on this, sorry.

 

[inforave]

Thank you all, you' ve been really helpful and that is just great! I'll get down to business with all the suggestions, thanks again!

 

[T. Valko]

Good day to all,

I' ve been scouring the Internet for a way to get vlookup to return not just the first value meeting the criteria but all of them but so far no luck.

PR1 100
PR2 300
PR3 500
PR4 700
PR1 200
PR4 800
PR3 600
PR2 400

PR1 Mutiple Values

To make my question clearer, lets say we have the above data in columns A and B. The lookup table is A1:B8 and the lookup value is in cell A10. Is there a way to get vlookup to return all the values from column B, matching the criteria (i.e. the lookup value in cell A10) and show the results in concatenated form in cell B10; In this simple example it should return 100,200.

I know this could be done using VBscript but I wonder if there is an easier way, using the available functions in Excel 2007.

I thought of one way it could be done but don't know how to express it in Excel language.

1. count the number of rows in the lookup range.

2. find the row number of the first occurence.

3. display the first occurence

4. search for the next occurence in a new range, defined as (A1+(row number of the first occurrence)):B8. For example if the first occurence was found in row 3, the new range would be defined as (A1+3:B8), i.e. A4:B8.

5. Repeat the process until we get the last value.

Any help would be greatly appreciated!!!

There is an example here:

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

 

[inforave]

Thanks!!!

 

[T. Valko]

Thanks!!!

You're welcome!

 

[cbilliau]

Using AND with IF inside this formula, How To?

Hi & welcome to the Board!

Excel 2010
	A	B	C	D	E	F
1	Data	Data			Condition	Data
2	PR1	100			PR1	100
3	PR2	300				200
4	PR3	500
5	PR4	700
6	PR1	200
7	PR4	800
8	PR3	600
9	PR2	400
Sheet1

Cell Formulas
Range	Formula
F2	{=IFERROR(INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$E$2,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($F$1:F1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

I am successfully using this formula for a current project but I want to expand it and base the formula's result on two conditions instead of one. As an example lets assume column C has the follow set of numbers in succession and align with data in columns A and B:

/C/
"Data"
.5
.7
.4
.6
.7
.9
.1
...

Everything else is the same, the original condition is still in E2 ("PR1") and the new second condition will be ".7" located in G2. How do I correctly use the AND after the IF in Sandeep's formula so that I end up with an answer that meets both conditions?

I tried

{=IFERROR(INDEX($B$2:$B$9,SMALL(IF(AND($A$2:$A$9=$E$2,$C$2:$C$9=$G$2),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($F$1:F1))),"")}

But it is only giving me the first number from column B (B2 "100") where it should give me the result from B6 ("200"). Where am I going wrong?

Thank you.

CB