Vlookup or index match with 2 or more variables

[jasonleewkd]

Hi folks, I have the above problem.

I'm trying to obtain price by looking at 2 criteria.

Occupied status	Address	Price			Occupied status	Address	Price

Vacant	        Address 1	100			Vacant 	        Address 1	{=index(C2:C3,match(1,(A2:A3=D2)*(B2:B3=E2),0))}

Vacant	        Address 2	200			Vacant 	        Address 2	

Also tried {=INDEX(C2:C3,MATCH(1,IF(A2:A3=D2,IF(B2:B3=E2,1)),0))}

The problem is that both (Vacant & Address 1) and (Vacant & Address 2) returns 100. Vacant and address 2 is supposed to return 200.

Any tips please?

Thank you

[dilipandey]

Hi Jason,

Use match function like match(d2&e2,a2:a3&b2:b3,0) in your formula and then confirm using ctrl shift enter. upload the sample workbook in case of any issues. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[jasonleewkd]

Hi Sir, your formula does not work also. I've attached a sample file. Thank you. My hair is falling out from all the pulling!!

[dilipandey]

Dear jason.. see in the attached file. its working

Book7.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[jasonleewkd]

Hi Dilip,

I've followed your every step but can't seem to get it.

I've copied your formula, selected 2 cells, entered the formula into the formula bar and CSE, but I can't get 200.

Is it the order that I'm entering the formula which is wrong?

Jason

[dilipandey]

HI Jason,

Out of those 2 cells.. you need to enter first one (top one) and there using CSE and then copy that down or press Ctrl + D (with above one selected) .. let me know if this works. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[jasonleewkd]

omg it works. My questions

1) why it does not work when i select 2 cells, and then paste the formula in the formula bar and CSE enter?

2) why does the formula {=index(C2:C3,match(1,(A2:A3=D2)*(B2:B3=E2),0))} not work as this is provided by Microsoft website?

Thank you

[dilipandey]

1) when you selected two cells and entered formula with CSE, it has converted formula as an array for both cells and since formula did not says when to include next value of array, hence it picked only first value i.e., first match only for both of the cells. So CSE should be entered by selecting one cell only unless you are applying FREQUENCY type of function where it converts all cells as an array for bucketing purpose.

2) can't comment on Microsoft
but if you revise the formula as shown below, it works

=INDEX(C2:C3,SUMPRODUCT(--(A2:A3=F2)*(B2:B3=G2)))

Regards,
DILIPandey

<click on below 'star' if this helps>