 |
 | 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 / July 2006Multiple VLookup Values
Hello All, I am wondering if someone can help me with a custom Macro using the VLOOKUP logic. I need to do something like: VLOOKUP(lookup_value1,lookup_value2,table_array,col_index_num,range_lookup) Where lookup_value1 AND lookup_value2 must be there for the item in col_index to be returned. So I guess I would need to let the macro which column lookup_value1 and lookup_value2 should test in? Does this make sense? Alex Yep. But you don't even need a macro to do this... You can use this kind of syntax: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) (still an array formula) > Hello All, > [quoted text clipped - 14 lines] > > Alex  SignatureDave Peterson Hi Dave, Thanks for the suggestion. Here's what the sheet data looks like that I am trying to get the information from... 1/30/2006 16:30 9 1 1/30/2006 17:00 11 1 1/30/2006 17:30 12 5 1/30/2006 18:00 9 2 1/30/2006 18:30 0 0 1/31/2006 6:00 0 0 1/31/2006 6:30 1 0 1/31/2006 7:00 5 1 1/31/2006 7:30 9 3 1/31/2006 8:00 10 1 1/31/2006 8:30 13 0 1/31/2006 9:00 10 1 1/31/2006 9:30 14 1 1/31/2006 10:00 12 1 I'd like to look up the date value but also lookup the time value and only return the value in the 3rd column if both the time and date match. The above items are just a sample of what I have, the sheet lists more days and repeats the times in each day. So: if date matches 01/31/2006 and time matches 7:00 the value in column 3 is returned of 5. I guess I need to better understand the INDEX and MATCH formulas. I tried to enter this into Excel but don't quite understand why it's not working. I'll try to breakout the formulas to see if it makes more sense. Alex Hi Dave, I've got it! Your formula was perfect! Thanks, Alex One strange thing: All work fine except for 11:30 AM. Any thoughts why? This often happens with time, it is due to arithmetic precision. Try this alternative =INDEX($C$1:$C$100,MATCH(1,(J1=$A$1:$A$100)*(TEXT(J2,"hh:mm")=TEXT($B$1:$B$1 00,"hh:mm")),0)) or even =INDEX($C$1:$C$100,MATCH(1,(J1=$A$1:$A$100)*(TEXT(J2,"hh:mm")=TEXT($B$1:$B$1 00,"hh:mm")),0)) SignatureHTH Bob Phillips (remove nothere from email address if mailing direct) > One strange thing: > > All work fine except for 11:30 AM. > > Any thoughts why? I've tried to apply this to some work I'm doing and I get #NA. This i my data: SSN Name Source Amount 999999999 John Doe 1 356.35 This is the formula I'm using: {=INDEX(Sheet1!D1:D1253, MATCH(1,(A5=Sheet1!A1:D1253)*(C5=1),0))} Any help is greatly appreciated I've tried to apply this to some work I'm doing and I get #NA. This i my data: SSN Name Source Amount 999999999 John Doe 1 356.35 This is the formula I'm using: {=INDEX(Sheet1!D1:D1253, MATCH(1,(A5=Sheet1!A1:A1253)*(C5=1),0))} Any help is greatly appreciated Try: {=INDEX(Sheet1!D1:D1253, MATCH(1,(A5=Sheet1!A1:A1253)*(C5=1),0))} HTH -- AP "Chris Berry" <Chris.Berry.2aqkaz_1152545653.7526@excelforum-nospam.com> a écrit dans le message de news: Chris.Berry.2aqkaz_1152545653.7526@excelforum-nospam.com... > I've tried to apply this to some work I'm doing and I get #NA. This is > my data: [quoted text clipped - 8 lines] > > Any help is greatly appreciated. Thanks I got it to work with: {=INDEX(Sheet1!$D$1:$D$1253, MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0))} Is there a trick to getting ISNA to work with Array Formulas? =if(isna(INDEX(Sheet1!$D$1:$D$1253, MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0)),INDEX(Sheet1!$D$1:$D$1253, MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0))) There isn't any trick, but you don't need to do that much work. You would only have to check to see if there was a match: =if(iserror(match(1,(...)*(...),0)),"Not found", index(...,match(1,(...)*(...),0)) It'll make the formula just a bit smaller. Another option would be to use multiple cells. Put the formula in one, then put: =if(iserror(x99),"Not Found",x99) You could hide that intermediate column that contains the "real" formula. > Thanks I got it to work with: > [quoted text clipped - 12 lines] > Chris Berry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36165 > View this thread: http://www.excelforum.com/showthread.php?threadid=511280 SignatureDave Peterson Is there a trick to getting ISNA to work with array formulas? This i what I have. {=if(isna(INDEX(Sheet1!$D$1:$D$1253, MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0))))
|
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.