Returning All Values that occur when a loookup should see multiple items in list

[Orange Womble]

Hi
I'm trying to run what I might call a "multiple vlookup" so something like this happens:
[TABLE="width: 142"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Green45[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]Green36[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]Green48[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Green71[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Blue10[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Blue92[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]Red84[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]Red63[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]Red88[/TD]
[/TR]
</tbody>[/TABLE]

[On another sheet] When I have a value "12" in a cell I would like to return the values Green45, Green71, Blue10, Blue92. [i could have these returned across numerous columns with the 1st in D, 2nd in E, etc, if that's easier - i could concatenate them anyway]
I'll be doing similar with all the other values and I think there may well be up to about 20 repetitions (maybe more) of each.
There's about 3,000 entries I need to lookup to return the values from a 10,000 row sheet.



As I'm in the presence of people cleverer than me I'll expand a little further but I think I can work out other stuff I'm trying to do - but ideas/pointers are always welcome maybe there's further reading I should do or there's a better direction I didn't think of or even know exists.
Actually what I'm trying to do is use invoice value to match information from two reports (one from the supplier & one from the buyer) for reasons I won't go into the invoice numbers are not a good enough.
On the first sheet the inv# maybe I341040 whilst on the second this may show as I341040-120909 & 1341040 & 341040/24 & Null & I341040(dup). I anticipate that, say, the 2nd and 4th would have a matched value [in another column], whilst the others do not match on value at all - clearly the 2nd is the 'right' invoice whilst the 4th is a duplication.
Naturally there are other invoices with the same value as those 2 so dragging what is listed as invoice # all together my eyes will perfrom the fuzzy logic to get me to a good match and i only have to actually go and check a few entries for dates and other things that I'm not sure i want to rely on entirely either...... maybe I should be doing a lookup that is based on the inv date + tolerance and inv value.... but I can't figure that out either

Many thanks for any and all thoughts
Jez

 

[Orange Womble]

There's always more than one way to get around an absence of knowledge!!
I think I may have figured out how to accomplish what i need with less hassle - I had forgotten about wild cards.
So something like this will allow me to return the inv value which will likely match..... [and i can easily check that with sheet val - returned val]

=vlookup("?"&(right(A1,(len(a1)-1)&"*",inv value,false) [i'll always use a false even in this situation!]

it doesn't find any duplications though.... but I think I'm going to assume they are true duplications so ignore them.
(I may well not use the wild first character to provide more solid matches, but then add it in where on isn't found)

I've wanted to lookup from all the duplications in a list before though {and gave up} so if there is a way please do share!!
Thanks for reading and i hope this helps someone else in some small way anyway.

 

[Robert Mika]

Hi
I'm trying to run what I might call a "multiple vlookup" so something like this happens:
[TABLE="width: 142"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Green45[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]Green36[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]Green48[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Green71[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Blue10[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Blue92[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]Red84[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]Red63[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]Red88[/TD]
[/TR]
</tbody>[/TABLE]

[On another sheet] When I have a value "12" in a cell I would like to return the values Green45, Green71, Blue10, Blue92. [i could have these returned across numerous columns with the 1st in D, 2nd in E, etc, if that's easier - i could concatenate them anyway]
I'll be doing similar with all the other values and I think there may well be up to about 20 repetitions (maybe more) of each.
There's about 3,000 entries I need to lookup to return the values from a 10,000 row sheet.



As I'm in the presence of people cleverer than me I'll expand a little further but I think I can work out other stuff I'm trying to do - but ideas/pointers are always welcome maybe there's further reading I should do or there's a better direction I didn't think of or even know exists.
Actually what I'm trying to do is use invoice value to match information from two reports (one from the supplier & one from the buyer) for reasons I won't go into the invoice numbers are not a good enough.
On the first sheet the inv# maybe I341040 whilst on the second this may show as I341040-120909 & 1341040 & 341040/24 & Null & I341040(dup). I anticipate that, say, the 2nd and 4th would have a matched value [in another column], whilst the others do not match on value at all - clearly the 2nd is the 'right' invoice whilst the 4th is a duplication.
Naturally there are other invoices with the same value as those 2 so dragging what is listed as invoice # all together my eyes will perfrom the fuzzy logic to get me to a good match and i only have to actually go and check a few entries for dates and other things that I'm not sure i want to rely on entirely either...... maybe I should be doing a lookup that is based on the inv date + tolerance and inv value.... but I can't figure that out either

Many thanks for any and all thoughts
Jez

Does this help:

Excel 2010
	A	B	C	D	E
1	Matched	Reference number		Excel 07/10	Excel 03
2	12	Green45	4	Green45	Green45
3	45	Green36	12	Green71	Green71
4	95	Green48		Blue10	Blue10
5	12	Green71		Blue92	Blue92
6	12	Blue10
7	12	Blue92
8	95	Red84
9	45	Red63
10	45	Red88
input

Cell Formulas
Range	Formula
C2	=COUNTIF(input!$A$2:$A$200,C3)
D2	{=IFERROR(INDEX($B$2:$B$200,SMALL(IF($A$2:$A$200=$C$3,ROW($C$3:$C$200)-ROW($C$3)+1),ROWS($C$3:C3))),"")}
D3	{=IFERROR(INDEX($B$2:$B$200,SMALL(IF($A$2:$A$200=$C$3,ROW($C$3:$C$200)-ROW($C$3)+1),ROWS($C$3:C4))),"")}
E2	{=IF(ROWS($C$3:C3)>$C$2,"",INDEX(input!$B$2:$B$200,SMALL(IF(input!$A$2:$A$200=$C$3,ROW($C$3:$C$200)-ROW($C$3)+1),ROWS($C$3:C3))))}
E3	{=IF(ROWS($C$3:C4)>$C$2,"",INDEX(input!$B$2:$B$200,SMALL(IF(input!$A$2:$A$200=$C$3,ROW($C$3:$C$200)-ROW($C$3)+1),ROWS($C$3:C4))))}
Press CTRL+SHIFT+ENTER to enter array formulas.