Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index formula and VLookup w/ Multiple Values
Hello,
I searched through the threads and tried using some of Biff's array formulas, but not with any success. I believe I'm missing a step or two. An excerpt of my data is as such: C D N O P 1 File # Dept File # 120004 120009 2 100121 120009 100120 N/A N/A 3 100127 120009 100121 N/A 120009 4 100137 120009 100129 N/A N/A 5 100145 120009 100137 N/A 120009 I think the best way for me to explain what I'm trying to do is to show you the formula I came up with before this post: =IF((VLOOKUP($N2,$C$2:$D$5,2,FALSE)=$O$1),$O$1,"N/A") This worked until I learned that Column C has duplicates. For example, File #100695 can belong to Dept 120009, 120063, and 120092. Any thoughts or suggestions? I have a feeling I'm making things more complicated than necessary. I'd greatly appreciate any help. Kind regards, Patrick |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index formula and VLookup w/ Multiple Values
Hi,
You may want to refer my article on the following link http://office.microsoft.com/en-us/ex...CL100570551033 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Patrick" wrote in message ... Hello, I searched through the threads and tried using some of Biff's array formulas, but not with any success. I believe I'm missing a step or two. An excerpt of my data is as such: C D N O P 1 File # Dept File # 120004 120009 2 100121 120009 100120 N/A N/A 3 100127 120009 100121 N/A 120009 4 100137 120009 100129 N/A N/A 5 100145 120009 100137 N/A 120009 I think the best way for me to explain what I'm trying to do is to show you the formula I came up with before this post: =IF((VLOOKUP($N2,$C$2:$D$5,2,FALSE)=$O$1),$O$1,"N/A") This worked until I learned that Column C has duplicates. For example, File #100695 can belong to Dept 120009, 120063, and 120092. Any thoughts or suggestions? I have a feeling I'm making things more complicated than necessary. I'd greatly appreciate any help. Kind regards, Patrick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index formula and VLookup w/ Multiple Values
Perhaps something like this?
In O2, normal ENTER: =--ISNUMBER(MATCH(1,INDEX(($C$2:$C$100=$N2)*($D$2:$D$ 100=O$1),),0)) Copy O2 to P2, fill down as far as required. This populates results as zeros or 1's where the file# in col N & the Dept specified in O1/P1 (the combination) is not found (zero) or found (1's) within the source cols C and D. Voila? Hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Patrick" wrote: I searched through the threads and tried using some of Biff's array formulas, but not with any success. I believe I'm missing a step or two. An excerpt of my data is as such: C D N O P 1 File # Dept File # 120004 120009 2 100121 120009 100120 N/A N/A 3 100127 120009 100121 N/A 120009 4 100137 120009 100129 N/A N/A 5 100145 120009 100137 N/A 120009 I think the best way for me to explain what I'm trying to do is to show you the formula I came up with before this post: =IF((VLOOKUP($N2,$C$2:$D$5,2,FALSE)=$O$1),$O$1,"N/A") This worked until I learned that Column C has duplicates. For example, File #100695 can belong to Dept 120009, 120063, and 120092. Any thoughts or suggestions? I have a feeling I'm making things more complicated than necessary. I'd greatly appreciate any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple vlookup; index/match | Excel Discussion (Misc queries) | |||
Multiple values (INDEX?) | Excel Worksheet Functions | |||
match/index using multiple values | Excel Worksheet Functions | |||
return multiple corresponding values using INDEX | Excel Worksheet Functions | |||
Multiple Column Index Number in VLookup | Excel Worksheet Functions |