 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007Adding third lookup value to index/match
I understand how Index/Match works with double lookups... But I cant quite get the third lookup down. Looking to add a third lookup value and range to this formula (range z) =INDEX(table, MATCH(x,range x,0), MATCH(y,range y,1)) Data is laid out like so... (Vertical column range is x) A B C D E F G H 1 range yyyyyyyyyyyyyy -> 2 range zzzzzzzzzzzzzz -> x x x x x x any suggestions...? How does the 3rd range relate? Need more specific details.  SignatureBiff Microsoft Excel MVP >I understand how Index/Match works with double lookups... But I cant > quite get the third lookup down. [quoted text clipped - 17 lines] > > any suggestions...? Thanx...! The first range will be x (vertical). (contains 4 letter codes) The first range will be y (horizontal) (contains dates). The first range will be z (horizontal) (contains times) Ok, so you probably want a specific time for a specific date. What does the setup look like? Can you post a sceencap? Is there a different date in every cell in range Y? What about the times in range Z? How are the times associated with a date? This should be no problem but I can't suggest something without knowing how the data is setup. SignatureBiff Microsoft Excel MVP > Thanx...! > > The first range will be x (vertical). (contains 4 letter codes) > The first range will be y (horizontal) (contains dates). > The first range will be z (horizontal) (contains times) X Search value is = FRUIT (cell g20) Y Search value is = 09:00 (cell g21) Z Search value is = Oct-4 (cell g22) Desired result = 16 Need to add criteria and range z. =INDEX(table, MATCH(x,range x,0), MATCH(y,range y,1)) Table starts with cell A1. B C D E A1 08:00 09:00 10:00 11:00 A2 Oct-4 Oct-4 Oct-3 Oct-3 APPLES 10 20 30 40 BANANAS 20 30 40 50 CHERRIES 60 70 80 90 DUNG 91 92 93 94 EGGPLANT 11 12 13 14 FRUIT 15 16 17 18 GRAPES 19 20 21 22 This works for me =INDEX(A1:I20,MATCH("fruit",A1:A20,0),MATCH(1,(A1:I1=TIME(9,0,0))*(A2:I2=DATE(2007,10,4)),0)) change the cell references accordingly to fit your size It needs to be entered with ctrl + shift & enter SignatureRegards, Peo Sjoblom >X Search value is = FRUIT (cell g20) > Y Search value is = 09:00 (cell g21) [quoted text clipped - 24 lines] > GRAPES 19 20 21 > 22 Thanx. Since I have cell references for the criteria I am searching for, could something like this work? =INDEX(A1:E13,MATCH(K2,A1:A13,0),MATCH(1,(A1:E1=K3)*(B2:E2=K4),1)) Yes if you put those values in K2 - K4 and enter the formula with ctrl + shift & enter it should work If it doesn't then there is no match meaning that somehow what you put in K3 and K4 might differ. You can easily check that by direct comparison like =K3=C1 where K3 holds 9:00 and presumably C1 does too. If it returns FALSE you will need to find if the values in A1:E1 are text, do the same for K4 and C2 SignatureRegards, Peo Sjoblom > Thanx. > > Since I have cell references for the criteria I am searching for, > could something like this work? > > =INDEX(A1:E13,MATCH(K2,A1:A13,0),MATCH(1,(A1:E1=K3)*(B2:E2=K4),1))
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.