Search code examples
excelexcel-formulaexcel-2010excel-2007worksheet-function

Excel function to Auto-Populate value in Column C based on the combination of values in column A and Column B

I have 3 columns with values filled in already in my metasheet. A combination of values in column A and column B makes the selection unique. I need to pull/return the value in column C for the values selected in columns A & B. for example: In sheet 1, I have the following data:

    country     Month    weather
1   USA         Jan       winter
2   USA         Feb       fall
3   USA         May       summer
4   China       Jan       summer
5   China       Feb       spring
6   China       May       fall
7   India       Jan       fall
8   India       Feb       summer
9   India       May       Rain

Now, say for a random row 25, I have A25 as a dropdown list with value selected ="India" and B25 as a dropdown list with value selected="Feb", in which case I would want C25 to have a dropdown list with the value in it being "Summer".

I tried this formula: 

=VLOOKUP(B25, OFFSET(B$1:C$9, MATCH(A25,A$1:A$9,0)-1, 0, 2, 2), 2, 0)

But this one gives me an error: "The list source must be a delimited list, or a reference to a single row or column". I did refer to this solution. But I get the above mentioned error as the data validation for C25 is a list.

Any suggestions/ideas on this would be helpful! Thank you!

Solution

  • If your sheet is set-up like this:

    enter image description here

    You can use:

    =INDEX(C2:C10,INDEX(MATCH(1,(A2:A10=E2)*(B2:B10=F2),0),0))