I try to look for a formula that I can return a value from a table matching with multiple criteria. The attached file is the sample problem what I want to solve. Please someone help me on this. Thanks.
Sample Problem.xlsx
I try to look for a formula that I can return a value from a table matching with multiple criteria. The attached file is the sample problem what I want to solve. Please someone help me on this. Thanks.
Sample Problem.xlsx
Hello, Welcome to the forum.
Try this,
=VLOOKUP(F12,$B$6:$J$9,MATCH(C12,C$3:J$3,0)+MATCH(E12,INDEX(C$5:J$5,MATCH(D12,INDEX(C$4:J$4,MATCH($C12,C$3:J$3,0)):INDEX(C$4:J$4,COLUMNS($C4:$J4)),0)):INDEX(C$5:J$5,COLUMNS($C5:$J5)),0))
EDIT: Use this one. Added a MATCH+
=VLOOKUP(F12,$B$6:$J$9,MATCH(C12,C$3:J$3,0)+MATCH(D12,$C$4:$J$4,0)+MATCH(E12,INDEX(C$5:J$5,MATCH(D12,INDEX(C$4:J$4,MATCH($C12,C$3:J$3,0)):INDEX(C$4:J$4,COLUMNS($C4:$J4)),0)):INDEX(C$5:J$5,COLUMNS($C5:$J5)),0)-1)
copy down.
Last edited by Haseeb Avarakkan; 12-25-2011 at 11:21 PM. Reason: Added a MATCH
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Hi cktung and welcome to the forum,
You will need an Array Formula and unmerge your row 3,4,5 data. See the attached for an answer. The attachment isn't uploading for me so put this formula in G2 and confirm the entry with a Control-Shift Enter.
Then pull the formula down into G13.Please Login or Register to view this content.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Repeat of above message - database error trying to attach a file that seems to worked above.
Haseeb A,
Thanks for your tutorial. However, I miss something that might cause a lot of problem to me. Could you please help me with that? Thanks
Sample Problem.xlsx
MarvinP,
Thanks for your idea. Could you please look at the recent problem posted? It is a little tricky if I try to apply the formula you suggested
Try this:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks