Index formula and VLookup w/ Multiple Values

[Patrick]

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

 

[Ashish Mathur]

Hi,

You may want to refer my article on the following link

http://office.microsoft.com/en-us/excel/HA012260381033.aspx?pid=CL100570551033

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Max]

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