Double V Lookup

[clares]

Hi All

I've giving up trying to work this one out. Can someone help me please?

I have two worksheets in a workbook

On the first worksheet sheet I have:

Column A (Account Code) and Column B (Address Code) Column C (wanting the address ref from second worksheet)

On the second worksheet I have 3 columns:

Column A (Account Code) and Column B (Address Code) Column C (Address Ref)

If the account code and address code match put the address ref in column C on first workbook?

I do hope I have explained myself.

Could anyone please help?


Kind regards

Peter

 

[mvptomlinson]

Try the array entered formula:

=INDEX(Sheet2!$C$1:$C$100,MATCH(Sheet1!$A$1&Sheet1!$B$1,Sheet2!$A$1:$A$100&Sheet2!$B$1:$B$100,0))

Array entered means you must press CTRL+SHIFT+ENTER after typing it, not just ENTER.

 

[Special-K99]

Or
(tested against 6 rows)

=INDEX(Sheet1!A$1:C$6,MATCH(B1,IF(Sheet1!A$1:A$6=A1,Sheet1!B$1:B$6,""),0),3)

Again Array formula so Ctrl-Shift-Enter

 

[clares]

Or
(tested against 6 rows)

=INDEX(Sheet1!A$1:C$6,MATCH(B1,IF(Sheet1!A$1:A$6=A1,Sheet1!B$1:B$6,""),0),3)

Again Array formula so Ctrl-Shift-Enter

Hi There

Should I enter this formula on sheet 1 or sheet 2. I see that you are always referencing sheet 1?

Kind regards

Peter

 

[clares]

Hi Special K-99

This is the formula but it returns a #N/A

=INDEX(AccountShippingAddress!A$2:C$4352,MATCH(B2,IF(AccountShippingAddress!A$2:A$2354=A2,AccountShippingAddress!B$2:B$2354,""),0),3)

Kind regards

Peter

 

[mvptomlinson]

Peter, did you try my formula? Replace Sheet1 and Sheet2 with your sheet names. They were a reference to your first sheet and second sheet in your original question.

 

[Special-K99]

Go with the mvptomlinson's suggestion

 

[clares]

Hi mvpTomlinson

I have tried with substituting and I still get the #N/A:

=INDEX([Sheet2]AccountShippingAddress!$C$2:$C$4532,MATCH([Sheet1]ShipAddress!$A$2&[Sheet1]ShipAddress!$B$2,[Sheet2]AccountShippingAddress!$A$2:$A$4532&[Sheet2]AccountShippingAddress!$B$2:$B$4532,0))

 

[mvptomlinson]

?? What are the references to [Sheet1] and [Sheet2] ??

If those aren't the names of your worksheets, take them out. They were placeholders for your actual sheet names.

 

[clares]

I'm placing the formula in C2 on sheet1?

Kind regards

Peter