Hi,
I've got a problem where I am trying to return a value if two criteria from one table match two from another.
I have included a example of my issue.
=INDEX(Time!D:D,MATCH(Data!A2,Time!A:A,0)*AND(MATCH(Data!C2,Time!C:C,0)))
I have had a look around lots of forums but cant get my head around what is wrong with my formula
Thanks in advance of any help
Try this array formula and remember to confirm it with Ctrl+Shift+Enter
=INDEX(Time!$D$2:$D$11,MIN(IF(Time!$A$2:$A$11=A2,IF(Time!$B$2:$B$11=B2,IF(Time!$C$2:$C$11=C2,ROW(Time!$D$2:$D$11)-ROW(Time!$D$2)+1)))))
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Thanks for your help.
Would you mind explaining how this formula works?
For instance I dont really understand what this part of the formula is doing )-ROW(Time!$D$2)+1)))))
Thanks again
ROW(Time!$D$2:$D$11)-ROW(Time!$D$2)+1 gives you row numbers for the rows which meet the logical criteria in IF statements. Select the cell which has this formula and press F2 and select ROW(Time!$D$2:$D$11)-ROW(Time!$D$2)+1 by dragging it in the formula bar and press F9 that will tell you what this formula is doing.
Re: index and match multiple columns on two different sheets
Thanks for your help.
I am having a liitle difficulty with using this formula on another sheet.
Please could you take a look and explain where ive gone wrong. Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks