Expert: Craig - 11/9/2009

Question
I have a list as follows:-
Column
A                  B              C
Reg        Branch            Comment
62663           6546           YesA
62663           817           YesB
69901           472           YesC
69901           372           YesD

There are 2 cells on another sheet.  One cell contains the
Reg and other contains the Branch.  Once I change the Reg number and the branch I want it to display the Comment next to the corresponding match.  i.e. if I enter reg as 69901 and branch as 472 then it would show YesC.  If I enter reg as 69901 and branch as 372 this would show YesD.

I can do lookup but this would only lookup 1 criteria and will only look at the 1st one found in list.  I need to go down the list till it matches column A 7 B and then to show column C.

Any help you can provide will be most helpful.

Thanks

Answer
Hi Habib,

If you know how to do vlookup, you are halfway there.  All you need to do is add a column to each sheet that combines (concatenates) columns A&B by formula to create a unique ID to return the value in column C.

Insert a column in front of column A and use this formula on new columns B&C (the old A&B):

=concatentate(B2,C2)

Do the same for your second sheet and use these new columns as the basis for your vlookup formula.

Good luck,

-Craig