Hi folks, I have the above problem.
I'm trying to obtain price by looking at 2 criteria.
Formula:
Please Login or Register to view this content.
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
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>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com
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!!
Dear jason.. see in the attached file. its working
Book7.xlsx
Regards,
DILIPandey
<click on below 'star' if this helps>
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
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>
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
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
Formula:Please Login or Register to view this content.
Regards,
DILIPandey
<click on below 'star' if this helps>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks