Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
One to many vlookup help!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
One to many vlookup help!
=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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
One to many vlookup help!
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
One to many vlookup help!
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)),"",INDE X(Sheet2!$A$2:$B$12,E3,2)) C3: =IF(ISERROR(INDEX(Sheet2!$A$2:$B$12,F3,2)),"",INDE X(Sheet2!$A$2:$B$12,F3,2)) D3: =IF(ISERROR(INDEX(Sheet2!$A$2:$B$12,G3,2)),"",INDE X(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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
One to many vlookup help!
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! -- hojeesum ------------------------------------------------------------------------ hojeesum's Profile: http://www.excelforum.com/member.php...o&userid=34770 View this thread: http://www.excelforum.com/showthread...hreadid=545318 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
One to many vlookup help!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |