Lookup with logic
 

I cannot figure out how to have Excel use logic to find data in a list like a
vlookup and then go to the next one on the list. Index & match don't work
nor does lookup or vlookup. Any ideas???

The first list would contain data like this:

Product ID # Amount Make
1111 1111-0001 $50.00
1111 1111-0010 $100.00
1111 1111-0050 $300.00
1122 1122-0022 $3,000.00
1122 1122-0024 $5,000.00
2112 2112-0002 $1,000.00
2112 2112-0066 $2,000.00
2112 2112-0099 $3,000.00

The second list contains this:

Product Make
1111 A
1111 BB
1122 C
2112 D
2112 E

I want to populate list one under the "Make" column with the information in
list two. So, for product #1111, I want it to find that product in list two
and then pull in the first Make of A. Then, I want the second item on list 1
to pull in the second item on list 2. When it gets to the 3rd product on the
list, I want to leave it blank since list two only has 2 possible options.
I'd want to be able to copy a formula on down so that it could keep doing
this.

Any ideas??

I had to use a helper column on your list2 (Assuming it was in G:H)

F G H
2 Unique ID Product Make
3 1-1111 1111 A
4 2-1111 1111 BB
5 1-1122 1122 C
6 1-2112 2112 D
7 2-2112 2112 E

In F2 I titled it Unique ID and in F3 put the following and copied down

=COUNTIF($G$3:G3,G3)&"-"&G3

Then in your Make column, and i'll assume your data is currently in Cols A:D
with the Make column being D, in cell D3 I used

=IF(ISNA(VLOOKUP(COUNTIF($A$3:A3,A3)&"-"&A3,$F$3:$H$7,3,0)),"",VLOOKUP(COUNTIF($A$3:A3,A3 )&"-"&A3,$F$3:$H$7,3,0))

and copied down

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Former Excel Queen" <Former Excel wrote in
message ...
I cannot figure out how to have Excel use logic to find data in a list like a
vlookup and then go to the next one on the list. Index & match don't work
nor does lookup or vlookup. Any ideas???

The first list would contain data like this:

Product ID # Amount Make
1111 1111-0001 $50.00
1111 1111-0010 $100.00
1111 1111-0050 $300.00
1122 1122-0022 $3,000.00
1122 1122-0024 $5,000.00
2112 2112-0002 $1,000.00
2112 2112-0066 $2,000.00
2112 2112-0099 $3,000.00

The second list contains this:

Product Make
1111 A
1111 BB
1122 C
2112 D
2112 E

I want to populate list one under the "Make" column with the information in
list two. So, for product #1111, I want it to find that product in list two
and then pull in the first Make of A. Then, I want the second item on list 1
to pull in the second item on list 2. When it gets to the 3rd product on the
list, I want to leave it blank since list two only has 2 possible options.
I'd want to be able to copy a formula on down so that it could keep doing
this.

Any ideas??



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004