One to many vlookup help!

Post an example of what formula you are using at the moment.
VLOOKUP will certainly do what you are asking

Michael M

"hojeesum" wrote:

>
> Hi All
>
> I have two sheets, here is an example of what I am looking at:
>
> SHEET 1:
>
> Scenario: Number 1 Number 2 Number 3
> Number X
> 130
> 10
> 10
> 33
> etc.
>
> SHEET 2:
>
> Scenario: Number:
> 1 2067896512
> 1 2098172651
> 1 4258712369
> 10 4258097209
> 10 5783992762
> 20 1891237762
> 33 2349785432
> 33 2131452332
> 33 9843527232
> 130 4354678976
> 130 7836284285
> etc.
>
> I would like to have sheet 1 pull unique numbers from sheet 2 for the
> different scenarios. I have been playing with vlookup and I am not sure
> if I am using the correct forumla to accomplish this.
>
> Thanks for all your help!
>
>
> --
> hojeesum
> ------------------------------------------------------------------------
> hojeesum's Profile: http://www.excelforum.com/member.php...o&userid=34770
> View this thread: http://www.excelforum.com/showthread...hreadid=545318
>
>

=VLOOKUP(Sheet1!A:A,Sheet2!A:B,2)

This formula seems to return some vaild data but the numbers are being repeated and not unique.

Thanks!

Hmmm ! I see what you're at now.
Your formula is OK, but I think unique numbers will need VBA.
and I'm afraid I can't help there, but I'm sure others will have their input

Regards
Michael M

"hojeesum" wrote:

>
> =VLOOKUP(Sheet1!A:A,Sheet2!A:B,2)
>
> This formula seems to return some vaild data but the numbers are being
> repeated and not unique.
>
> Thanks!
>
>
> --
> hojeesum
> ------------------------------------------------------------------------
> hojeesum's Profile: http://www.excelforum.com/member.php...o&userid=34770
> View this thread: http://www.excelforum.com/showthread...hreadid=545318
>
>

Here's a way of doing it with formulae. I've used the same test data as
you provide, so this occupies A2 to B12 of Sheet2 with a header row. In
Sheet1 you have two header rows, so your number 130 is in A3. You need
to use 3 helper columns - I've used E, F and G, and these could be
hidden if you wish. Enter these formulae in the cells specified:

B3:
=IF(ISERROR(INDEX(Sheet2!$A$2:$B$12,E3,2)),"",INDEX(Sheet2!$A$2:$B$12,E3,2))
C3:
=IF(ISERROR(INDEX(Sheet2!$A$2:$B$12,F3,2)),"",INDEX(Sheet2!$A$2:$B$12,F3,2))
D3:
=IF(ISERROR(INDEX(Sheet2!$A$2:$B$12,G3,2)),"",INDEX(Sheet2!$A$2:$B$12,G3,2))
E3: =MATCH(A3,Sheet2!A$2:A$12,0)
F3: =MATCH(A3,INDIRECT("Sheet2!A$"&(E3+2)&":A$12"),0)+E3
G3: =MATCH(A3,INDIRECT("Sheet2!A$"&(F3+2)&":A$12"),0)+F3

These all refer to a range going up to row 12 - if you have, say, 50,
then you can do Find and Replace to change $12 to $50. Highlight these
6 cells and copy them down for as many entries as you have in column A.
I've used slightly different test data in Sheet1 - here's the results,
with E, F and G not shown:

Scenario Number 1 Number 2 Number 3
Number X
130 4354678976 7836284285
10 4258097209 5783992762
2
33 2349785432 2131452332 9843527232
20 1891237762

I think this is what you wanted.

Hope this helps.

Pete

Thanks Pete,

That was a really clever idea to use columns E,F,G to reference the positions. I'm still having problems populating data though, my number rows are giving me a "#NAME?" error. Do you have any idea what I might be doing wrong? Thanks for the help!