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!

 

[Guest]

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

Michael M

 

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

 

[Guest]

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

 

[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