Grits and Shins Posted January 10, 2008 Share Posted January 10, 2008 Anybody know how I can use the VLOOKUP function matching on more than 1 column? Say for example on sheet 1 column 1 contains state names, column 2 contains city names and columns 3 thru whatever contain data On sheet 2 column 1 contains state nam, column 2 contains city names I'd like to do a VLOOKUP where I retrieve date from sheet 2 where BOTH the state and city for a particular row match. Quote Link to comment Share on other sites More sharing options...
Atlanta Cracker Posted January 10, 2008 Share Posted January 10, 2008 Anybody know how I can use the VLOOKUP function matching on more than 1 column? Say for example on sheet 1 column 1 contains state names, column 2 contains city names and columns 3 thru whatever contain data On sheet 2 column 1 contains state nam, column 2 contains city names I'd like to do a VLOOKUP where I retrieve date from sheet 2 where BOTH the state and city for a particular row match. Are there that many cities in multiple states? If you must do that I think a Pivot table will be what you're looking for. Quote Link to comment Share on other sites More sharing options...
Puddy Posted January 10, 2008 Share Posted January 10, 2008 Blitz - I would add a new column and combine the two cells. Unless you need the data separate for a reason. i.e. in cell C1 enter this formula (=A1&B1). Quote Link to comment Share on other sites More sharing options...
Grits and Shins Posted January 11, 2008 Author Share Posted January 11, 2008 Blitz - I would add a new column and combine the two cells. Unless you need the data separate for a reason. i.e. in cell C1 enter this formula (=A1&B1). Yeah ... this is the way I have typically done it in the past ... I was wondering if there was a more graceful way to do it Quote Link to comment Share on other sites More sharing options...
Big Country Posted January 11, 2008 Share Posted January 11, 2008 (edited) Possibly a nested series of VLookups within an If..Then function. Something like if VLookup for state returns a value, then perform Vlookup on city (or city then state depending on how you need to do it), retrieving the value you want. I haven't tried testing this, but in theory it may work. ETA: Not sure if that works after playing around a little bit, but perhaps someone knows of a way based off of that thinking. Edited January 11, 2008 by Big Country Quote Link to comment Share on other sites More sharing options...
Grits and Shins Posted January 11, 2008 Author Share Posted January 11, 2008 Possibly a nested series of VLookups within an If..Then function. Something like if VLookup for state returns a value, then perform Vlookup on city (or city then state depending on how you need to do it), retrieving the value you want. I haven't tried testing this, but in theory it may work. ETA: Not sure if that works after playing around a little bit, but perhaps someone knows of a way based off of that thinking. Yeah I have tried to nested IF and VLOOKUP approach ... the problem is that there didn't appear to be an easy way to verify that BOTH the state and city match ON THE SAME row. Quote Link to comment Share on other sites More sharing options...
Big Country Posted January 11, 2008 Share Posted January 11, 2008 From an Access forum, but in their Excel subforum Is it possible to do a VLOOKUP using two lookup fields? For example: A spreadsheet has two columns titled Last Name and First Name. The lookup spreadsheet has the same fields. There are several of the same last names so the secondary lookup needs to be on the first name. Response: If the Last Name is in column A and First Name in column B, lookup value in column C, lookup names in D1 and E1: =INDEX(C1:C3,MATCH(D1&E1,A1:A3&B1:B3,0)) I'll keep looking to see if I can find any other potential solutions Quote Link to comment Share on other sites More sharing options...
Big Country Posted January 11, 2008 Share Posted January 11, 2008 This thread may help too Quote Link to comment Share on other sites More sharing options...
Puddy Posted January 12, 2008 Share Posted January 12, 2008 Yeah ... this is the way I have typically done it in the past ... I was wondering if there was a more graceful way to do it Wear a tutu while you enter the formula Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.