Lookup formula to match against two columns

I have 3 columns, A,B and C.
B is a Master List of locations.
C is a Project list and is a dependent list to B.

I need a formula in column A to lookup and match B to C and return an account number from AccountNums (Named Range).

Some of the project names in column C are the same, and my current formula returns the wrong account number because it only looks at column C and matches the first project it comes to with the selected name. There may be 10 projects with the name ENHANCEMENT at 10 different locations.

My current formula:

=IF(C12="","",VLOOKUP(C12, AccountNums,2,FALSE))

Try this formula....
and change it your requirement
drag it down to cover B3, B4 and so on...

Formula:

=index(AccountNums,match(B2,$C$2:$C$100),1)

Waiting for *

Here is an example.
Thank you.

HI,

Find the updated file.

Punnam

Thanks Punnam. I appreciate you assistance.
That works great!

Hi WFP111,

Welcome .

Punnam

HI,

if u r question answered mark the thread solved using thread tools TAB.

Punnam

Punnam,
If you are still available or if someone else may be able to address a new issue.
This Formula works great in a simple table where data is manually typed in. However, I receive an ERROR when I place the formula in my master workbook.

"=IFERROR(INDEX(Lists!$C$2:$C$13,MATCH(PROJECTS!B10&PROJECTS!C10,Lists!$A$2:$A$13&Lists!$B$2:$B$13,0))," ")"

My "Lists!" tab actually reads from another workbook. Therefore, I believe it may be the cause for the ERROR.

Hi,

Can u post a masterwork book with out confidential data ?

punnam

Thanks Punnam.
I attached the ProjectsWorkbook and the ProjectsList that it reads from.

Hi,

Small change in the formula ,
1) Lists!B10&Lists!C10 should be replaced with C4&D4
2) FOr array it is necessary to use Ctr+Shift+Enter
=IFERROR(INDEX(Lists!$C$2:$C$22,MATCH(C4&D4,Lists!$A$2:$A$22&Lists!$B$2:$B$22,0)),"error")
Punnam