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.
