Vertical search multiple values

Question

Hey,&nbsp;I have a little problem and I can’t get the formule to work.&nbsp; &nbsp; &nbsp; &nbsp;A &nbsp; &nbsp; &nbsp; &nbsp;B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C1 Bob &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp;Januari &nbsp;2 Bob &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp;Februari3 Bob &nbsp; &nbsp; &nbsp; 30 &nbsp; &nbsp; &nbsp;March&nbsp;4 Ann &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp;Januari5 Ann &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp;Februari&nbsp;6 Ann &nbsp; &nbsp; &nbsp; 30 &nbsp; &nbsp; &nbsp;March&nbsp;&nbsp;&nbsp; &nbsp; &nbsp;A &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; &nbsp; C1 Bob &nbsp; &nbsp; &nbsp;302 Ann &nbsp; &nbsp; &nbsp;20&nbsp;The cells in column C has to have a formula that says: March for Bob 30 and februari for Ann 20.&nbsp;Thanks for the help!&nbsp;

oliverscheurich · Answer

Dejongbrian&nbsp;=INDEX(sheet1!$C$1:$C$6,MATCH(1,(sheet2!A1=sheet1!$A$1:$A$6)*(sheet2!B1=sheet1!$B$1:$B$6),0))&nbsp;If the sheetnames are "sheet1" and "sheet2" you can enter this formula in cell C1 of sheet2. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

dejongbrian · Answer

Thank You for your answer, but it does not work. I've checked it multiple times and i still get #N/B.

oliverscheurich · Answer

Dejongbrian&nbsp;Does it work when you open the attached file?

my · Answer

So to make it less confusing let's say it's
D E F
1 Bob 30
2 Ann 20

In F1 (Excel 365):
=XLOOKUP(D1&E1,A1:A6&B1:B6,C1:C6)

In F1:
=INDEX(C1:C6,MATCH(D1&E1,A1:A6&B1:B6,0))

Forum Discussion

Vertical search multiple values

Share

Resources