CODEWITHSUNDEEP
excelexcel-formula
user8107531
user8107531

Reputation: 27

Lookup using multiple columns and values

I have a sheet filled with data like below:

Gender MS       Age Value
Male   Married  21  1
                22  2
                23  3
                24  4
Male   Single   21  5
                22  6
                23  7
                24  8
Female Married  21  9
                22  10
                23  11
                24  12

Now there's this other sheet where the user will only be inputting the column "Gender", "MS", and "Age" values and the excel formula should be fetching the associated factor. Please help me with one such formula.

For e.g. If the user puts Male, Single and 23, the formula should return 7.

Upvotes: 0

Views: 396

Answers (2)

JvdV
JvdV

Reputation: 75840

Here is an example on your sample data:

enter image description here

Formula in G4:

=INDEX(D1:D13,MATCH(1,(ROW(C1:C13)>=MATCH(1,(A1:A13=G1)*(B1:B13=G2),0))*(C1:C13=G3),0))

Enter through CtrlShiftEnter

Upvotes: 1

zeerozeroone
zeerozeroone

Reputation: 15

You can use an array formula for this. I am assuming the blank data under Gender and MS are filled in.

=INDEX(Sheet1!A:D,MATCH(1,(Sheet2!A2=Sheet1!A:A)*(Sheet2!B2=Sheet1!B:B)*(Sheet2!C2=Sheet1!C:C),0),4)

After entering the formula, don't press enter! It is Ctrl+Shift+Enter to make this an array formula.

The formula given compares the values in cells A2, B2 and C2 of Sheet2 with the data held in columns A to D of Sheet1, and outputs the value of the fourth column of the appropriate row.

Upvotes: 0

Related Questions