One to many vlookup help!

[hojeesum]

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!

[Michael M]

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
>
>

[hojeesum]

=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!

[Michael M]

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
>
>

[Pete_UK]

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

[hojeesum]

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!

[Pete_UK]

Sorry, I missed this yesterday - it worked for me when I tested it out,
and I pasted the formulae directly to the newsgroups.

All I can advise is to check your spellings thoroughly - #NAME? implies
that Excel cannot interpret a function, so you may have misspelt
INDIRECT or ISERROR or INDEX or MATCH (or even IF !). Check also that
you have all the colons in the range references.

Hope this helps.

Pete