Need to retrieve data from 3rd column based upon criteria in first 2 columns

[tdd903]

Hello,

I have a table of data where the first column is the month of the year, usually in chronological order. There are up to ten listings of each month. In the second column there are names of sales people. Some repeat month to month, others do not. The third column is a code consisting of two letters and two numbers.

I wish to retrieve the info from the third column (the code) based upon the month and the sales person but, I have not been successful. Vlookup and Index(Match) haven't worked for me so far. Can anyone explain how I could accoomplish this? I am not well versed in Macros or VBA if those are options.

Thank you to any who reply.

Todd

Louisville, KY

 

[Hui]

Todd

Assuming your data is in A2:C100

and the Month you want to lookup is in E4 and Name in E5

try the following array formula

=+INDEX(A2:C100,MATCH(E4&E5,A2:A100&B2:B100,0),3)

and enter with Ctrl Shift Enter

Adjust ranges to suit

 

[keymaster]

Todd,


Assuming the three columns are A,B and C (Month, Sales person and code in that order)


first add a fourth column to your table (and hide it if you dont want it to be seen by users). Write the formula =a1&"-"&b1


now, use the following formula for looking based on 2 columns


=index(c1:c100,match(month&"-"&salesman,d1:d100,0))

 

[tdd903]

Chandoo,

Thank you, it worked well.

Hui,

I could not get yours to work. Could be user error though... Thank you for your response.

 

[Hui]

Todd

Did you press Ctrl Shift Enter after entering the formula ?

Not just Enter.