MrExcel Publishing
Your One Stop for Excel Tips & Solutions

vlookup help


Posted by steve w on September 14, 2001 8:42 AM

Can someone help
I want to find multiple matches of the same value

A B C
1 10
1 20
1 30
2 40
2 50
2

example
I want to search for 1 then return whats in column b
then search for 1 again and find the next one down not finding the first one then return that column b

anyone have an idea
thanks steve w


Posted by Juan Pablo on September 14, 2001 8:55 AM

Use Autofilters...

Posted by Mark W. on September 14, 2001 9:11 AM

Why don't you...

...use an array formula such as...

{=IF(A1:A5=1,B1:B5,#N/A)}

Posted by steve w on September 14, 2001 1:57 PM

Re: Why don't you...(I need to use vlookup to search a big list and the values to top of another sheet

Posted by Aladin Akyurek on September 16, 2001 10:26 AM

Re: Why don't you...(I need to use vlookup to search a big list and the values to top of another sheet

Steve,

I still don't understand why the formula Mark suggested doesn't satisfy your situation.

Having said all that, here is a different but complicated approach to your problem.

I'll assume the following sample data in A2:B7 in a sheet named x (I believe you want to do the retrieval from a different worksheet).

{1,10;1,20;1,30;2,40;2,50;1,60}

What follows all regards sheet x. So activate x.

Activate Insert|Name|Define.
Enter MaxNum as name in the Names in Workbook box.
Enter as formula in the Refers To box:

=9.99999999999999E+307

Activate Add. Don't leave the Define Name window yet.

Enter MaxRecs in the Names in Workbook box.
Enter as formula in the Refers To box:

=MATCH(MaxNum,x!$A:$A)

Activate Add. Don't leave the Define Name window yet.

Enter LVALUES (from lookup values) in the Names in Workbook box.
Enter as formula in the Refers To box:

=OFFSET(x!$A$2,0,0,MaxRecs-1,1)

Activate Add. Don't leave the Define Name window yet.

Enter RVALUES (from retrieval values) in the Names in Workbook box.
Enter as formula in the Refers To box:

=OFFSET(x!$B$2,0,0,MaxRecs-1,1)

Activate Add. Don't leave the Define Name window yet.

Enter SDATA (from source data) in the Names in Workbook box.
Enter as formula in the Refers To box:

=OFFSET(x!$A$2,0,0,MaxRecs,2)

Activate OK.

Activate the sheet wherefrom you intend to retrieve data from SDATA.

In A2 enter: 1 (the value of which you want to retrieve all associated values)

In B2 enter: =IF(COUNTIF(LVALUES,$A$2)>=1,VLOOKUP($A$2,SDATA,2,0),"")

In B3 enter: =IF(COUNTIF(LVALUES,$A$2)>=COUNT(B$2:B2)+1,VLOOKUP($A$2,INDIRECT("x!"&ADDRESS(SUMPRODUCT((LVALUES=$A$2)*(RVALUES=B2)*(ROW(LVALUES)))+1,1)&":"&ADDRESS(MaxRecs,2)),2,0),"")

Copy down the formula of B3 as far as you need.

Cheers.

Aladin

PS. I hope you're not going to start another thread on this question.