Need help with Advance Lookup Formula!!!!!!!!!!!

CJANG

New Member
Joined
Dec 26, 2005
Messages
3
I need help creating a complex lookup formula that help me lookup data in mutiple column. Example:


Pro ID C-1 C-2 C-3
050001 8250 5251 5252
050002 9001 9002 9003
Lookup Table

Given Lookup Formula
CustID Pro ID
8250 050001
8251 050001
5252 #N/A
9001 #N/A
9002 050002
9003 050002

Here the situation. I have 3 Customers i.e. 8250, 5251, 5252 assoicated with Project ID #050001. I was given a Customer ID 8250, 5251 etc. I was able to create a lookup formula to lookup the left most column 1 with +INDEX(Pro ID,MATCH(Pro ID,CustomerID1,2,3,0)), that gave me 050001 but it will not give me 5251 nor 5252 with this formula. How can I create a single formula that helps match any of the 3 customer ID associated with Pro ID 050001 or 050002 and bring me back Pro ID 050001 with a formula.

Greatly Appreciated if Any one could resovle this problem.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Watch MrExcel Video

Forum statistics

Threads
1,118,726
Messages
5,573,837
Members
412,552
Latest member
sfay
Top