Orange Womble
New Member
- Joined
- Oct 13, 2009
- Messages
- 11
Hi
I'm trying to run what I might call a "multiple vlookup" so something like this happens:
<tbody>
</tbody>
[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
I'm trying to run what I might call a "multiple vlookup" so something like this happens:
A | B | |
1 | 12 | Green45 |
2 | 45 | Green36 |
3 | 95 | Green48 |
4 | 12 | Green71 |
5 | 12 | Blue10 |
6 | 12 | Blue92 |
7 | 95 | Red84 |
8 | 45 | Red63 |
9 | 45 | Red88 |
<tbody>
</tbody>
[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