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

 

[clares]

Yes I realised that but when I hit CTRL+SHIFT+ENTER excel (2003) showed me another box asking what worksheet the references were so I clicked them and that's what it gave me?

Kind regards

Peter

 

[mvptomlinson]

If your first worksheet is named "ShipAddress" (no spaces, nothing different) and your second worksheet is named "AccountShippingAddress", and you're placing the formula in the "ShipAddress" worksheet, then you should be able to use:

=INDEX(AccountShippingAddress!$C$2:$C$4532,MATCH($A$2&$B$2,AccountShippingAddress!$A$2:$A$4532&AccountShippingAddress!$B$2:$B$4532,0))

Copy that formula, then on your worksheet select cell C2 (on the ShipAddress sheet). Press F2 to start editing that cell (delete any current formulas) and then paste this formula. Press CTRL+SHIFT+ENTER to confirm.

If you still get an error, something is wrong with your workbook, or there is no match to the values you're looking up.

 

[clares]

OK thank you for your help anyway. Really appreciate it.

Kind regards

Peter

 

[mvptomlinson]

Does that mean it worked? If not, see my signature for a site to upload a sample workbook. (Mediafire preferred.)

 

[clares]

Hi mvptomlinson

Perfect thank you very much indeed.

I had to trim() one of the account codes as it had spaces at the end and I have to convert some of the values to numbers. I had to take change $A$2&$B$2 to A2&B2 then run like you said it would.

Very grateful indeed.

Kind regards

Peter