Lookup Function Not in Ascending Order
Is there any other way of going about getting the results that the lookup
function obtains without the list havng to be in ascending order? I have a pretty large database of company's names and when I make a new record and input the country I want the values for postal code, subsequent postal cost, best time to call, hour difference, etc, to all be pre-populated based on previuos records for that country. But my excel sheet will be subject to new ordering a lot, so Lookup will not work. I've found some formulas for IF but I have more than 7 conditions. Thank you to anyone who may be able to help me. |
Lookup Function Not in Ascending Order
Please give further details. It is possible to use VLOOKUP and HLOOKUP
with the data not sorted - set the 4th parameter to FALSE or 0 to look for an exact match. Hope this helps. Pete On Jul 29, 11:49*am, Jessica Donadio wrote: Is there any other way of going about getting the results that the lookup function obtains without the list havng to be in ascending order? *I have a pretty large database of company's names and when I make a new record and * input the country I want the values for postal code, subsequent postal cost, best time to call, hour difference, etc, to all be pre-populated based on previuos records for that country. *But my excel sheet will be subject to new ordering a lot, so Lookup will not work. *I've found some formulas for IF but I have more than 7 conditions. *Thank you to anyone who may be able to help me. * * * |
Lookup Function Not in Ascending Order
thank you so much for your quick response! Your post help me clear up my
uncertainty with the previous posts I read in regards to VLookup, I didn't understand the 3rd parameter indicated the 1st, 2nd, 3rd, column, and that the table was an area you could select. Adding the logic test was just what I needed to do. |
Lookup Function Not in Ascending Order
On Tue, 29 Jul 2008 03:49:02 -0700, Jessica Donadio
wrote: Is there any other way of going about getting the results that the lookup function obtains without the list havng to be in ascending order? I have a pretty large database of company's names and when I make a new record and input the country I want the values for postal code, subsequent postal cost, best time to call, hour difference, etc, to all be pre-populated based on previuos records for that country. But my excel sheet will be subject to new ordering a lot, so Lookup will not work. I've found some formulas for IF but I have more than 7 conditions. Thank you to anyone who may be able to help me. It seems as if you could use an exact match. Depending on how your data is set up, you could probably use VLOOKUP or HLOOKUP with FALSE entered for the optional Range_lookup argument. This would require an exact match. If that does not work, post back with more information, as the use of INDEX and MATCH (with the Exact parameter within the MATCH function) should be adaptable to your data. --ron |
Lookup Function Not in Ascending Order
Glad to be of help - thanks for feeding back.
Pete On Jul 29, 12:25*pm, Jessica Donadio wrote: thank you so much for your quick response! *Your post help me clear up my uncertainty with the previous posts I read in regards to VLookup, I didn't understand the 3rd parameter indicated the 1st, 2nd, 3rd, column, and that the table was an area you could select. *Adding the logic test was just what I needed to do. * |
Lookup Function Not in Ascending Order
Well the only snag is that when I paste the values down the column it
progressively causes the top rows not to be included in the range of data. However, I still want to retain the relative cell value of the first parameter As an example I have countries listed in column H and then the corresponding postal zones in column I (whose values are either 1,2,3, or Italy). Vlookup works, but if I start a new record 500 rows down, the cell reference for the top of the range will move likewise, so that it may not find the country value if there is only one located towards the top of the list. Is there a way to incorporate both absolute and relative cell referencing in the same function? Or, with index and match, how would I go about it to simply return the respective postal zones for each country? Thanks again! "Ron Rosenfeld" wrote: On Tue, 29 Jul 2008 03:49:02 -0700, Jessica Donadio wrote: Is there any other way of going about getting the results that the lookup function obtains without the list havng to be in ascending order? I have a pretty large database of company's names and when I make a new record and input the country I want the values for postal code, subsequent postal cost, best time to call, hour difference, etc, to all be pre-populated based on previuos records for that country. But my excel sheet will be subject to new ordering a lot, so Lookup will not work. I've found some formulas for IF but I have more than 7 conditions. Thank you to anyone who may be able to help me. It seems as if you could use an exact match. Depending on how your data is set up, you could probably use VLOOKUP or HLOOKUP with FALSE entered for the optional Range_lookup argument. This would require an exact match. If that does not work, post back with more information, as the use of INDEX and MATCH (with the Exact parameter within the MATCH function) should be adaptable to your data. --ron |
Lookup Function Not in Ascending Order
Wow, forget that, right after I typed that I saw below my thread what the $
sign means before the cell. All is good. I would still be curious about how this could be achieved through index/match and what would be the advantages to doing so, but otherwise, thank you all! "Jessica Donadio" wrote: Well the only snag is that when I paste the values down the column it progressively causes the top rows not to be included in the range of data. However, I still want to retain the relative cell value of the first parameter As an example I have countries listed in column H and then the corresponding postal zones in column I (whose values are either 1,2,3, or Italy). Vlookup works, but if I start a new record 500 rows down, the cell reference for the top of the range will move likewise, so that it may not find the country value if there is only one located towards the top of the list. Is there a way to incorporate both absolute and relative cell referencing in the same function? Or, with index and match, how would I go about it to simply return the respective postal zones for each country? Thanks again! |
Lookup Function Not in Ascending Order
Glad you got it sorted in the end. You can used named ranges to avoid
the problems of absolute addresses. The INDEX/MATCH approach would be something like this: =INDEX(return_column,MATCH(lookup,sought_column,0) ) to replace: =VLOOKUP(lookup,table,column,0) If you are using VLOOKUP the sought item has to be in the left-most column of the table, but with INDEX/MATCH the sought_column can be to the right of the return_column. Hope this helps. Pete On Jul 29, 2:36*pm, Jessica Donadio wrote: Wow, forget that, right after I typed that I saw below my thread what the $ sign means before the cell. *All is good. I would still be curious about how this could be achieved through index/match and what would be the advantages to doing so, but otherwise, thank you all! "Jessica Donadio" wrote: Well the only snag is that when I paste the values down the column it progressively causes the top rows not to be included in the range of data. * However, I still want to retain the relative cell value of the first parameter As an example I have countries listed in column H and then the corresponding postal zones in column I (whose values are either 1,2,3, or Italy). *Vlookup works, but if I start a new record 500 rows down, the cell reference for the top of the range will move likewise, so that it may not find the country value if there is only one located towards the top of the list. *Is there a way to incorporate both absolute and relative cell referencing in the same function? Or, with index and match, how would I go about it to simply return the respective postal zones for each country? *Thanks again!- Hide quoted text - - Show quoted text - |
Lookup Function Not in Ascending Order
On Tue, 29 Jul 2008 06:36:00 -0700, Jessica Donadio
wrote: Wow, forget that, right after I typed that I saw below my thread what the $ sign means before the cell. All is good. I would still be curious about how this could be achieved through index/match and what would be the advantages to doing so, but otherwise, thank you all! The advantage to index/match is that the lookup value does not need to be to the left of, or above the returned value. Whereas with index/match, the lookup value location is irrelevant. --ron |
Lookup Function Not in Ascending Order
So if I have cities filling column E (to E1236) and their respective
countries in column H (to H1236) How do I write the function so that in a new record the country value automatically matches the city I input? I've tried this, but no dice: =INDEX(H:H,MATCH(H10,E:E,0)) "Pete_UK" wrote: Glad you got it sorted in the end. You can used named ranges to avoid the problems of absolute addresses. The INDEX/MATCH approach would be something like this: =INDEX(return_column,MATCH(lookup,sought_column,0) ) to replace: =VLOOKUP(lookup,table,column,0) If you are using VLOOKUP the sought item has to be in the left-most column of the table, but with INDEX/MATCH the sought_column can be to the right of the return_column. Hope this helps. Pete On Jul 29, 2:36 pm, Jessica Donadio wrote: Wow, forget that, right after I typed that I saw below my thread what the $ sign means before the cell. All is good. I would still be curious about how this could be achieved through index/match and what would be the advantages to doing so, but otherwise, thank you all! "Jessica Donadio" wrote: Well the only snag is that when I paste the values down the column it progressively causes the top rows not to be included in the range of data. However, I still want to retain the relative cell value of the first parameter As an example I have countries listed in column H and then the corresponding postal zones in column I (whose values are either 1,2,3, or Italy). Vlookup works, but if I start a new record 500 rows down, the cell reference for the top of the range will move likewise, so that it may not find the country value if there is only one located towards the top of the list. Is there a way to incorporate both absolute and relative cell referencing in the same function? Or, with index and match, how would I go about it to simply return the respective postal zones for each country? Thanks again!- Hide quoted text - - Show quoted text - |
Lookup Function Not in Ascending Order
Well, with this formula you are trying to match H10 with column E, and
if it finds a match then get the corresponding entry from column H. Are you sure you are putting the new entry into H10? Wouldn't it belong in E10? I'm not really sure what you are trying to do - could you elaborate a bit further? Pete On Aug 1, 6:53*pm, Jessica Donadio wrote: So if I have cities filling column E (to E1236) and their respective countries in column H (to H1236) How do I write the function so that *in a new record the country value automatically matches the city I input? *I've tried this, but no dice: =INDEX(H:H,MATCH(H10,E:E,0)) |
Lookup Function Not in Ascending Order
Well. I had succeeded with Vlookup, but then realized when I add a new
record and it is reordered, the values will not be shown because the data is no longer above the record. So I was trying to add a new record near the top of the table (Row 10) to verify that my formula will work. It's essentially like this: E H Citty Country Los Angeles USA New York USA ? ? Milan Italy Barcelona Spain If I am to input "Milan" into E?, I want H? to be pre-populated. But I'm a bit unclear with this =INDEX(return_column,MATCH(lookup,sought_column,0) ) so... =Index(H:H,MATCH(E?,E:E,0)) does not seem to be correct. Thank you for all your help, this will really help me out at work, but I don't always have a lot of time to dedicate to experiment. Jessica "Jessica Donadio" wrote: So if I have cities filling column E (to E1236) and their respective countries in column H (to H1236) How do I write the function so that in a new record the country value automatically matches the city I input? I've tried this, but no dice: =INDEX(H:H,MATCH(H10,E:E,0)) |
Lookup Function Not in Ascending Order
If I am to input "Milan" into E?, I want H? to be pre-populated. But I'm a
bit unclear with this =INDEX(return_column,MATCH(lookup,sought_column,0) ) so... =Index(H:H,MATCH(E?,E:E,0)) does not seem to be correct. Thank you for all your help, this will really help me out at work, but I don't always have a lot of time to dedicate to experiment. Well, if I'm reading you right and this formula goes in the cells in H, it won't work easily since that is potentially circular or at least messy. You'd have to have all your locations actually typed into the first, say, 100 rows, then subsequent rows could access those first 100 with whatever lookup will work. And if you come up with a new city sometime, no lookup will work at all since it is the first time you typed it in, there's nothing to look up. You might want to have the city/country list somewhere else to reference. Perhaps put the location data far off to the right in BA:BB. Then your original idea of a VLOOKUP would work. But regardless, on your INDEX/MATCH question, change the INDEX range to the whole range and put in a column reference. So this checks column E, then returns an answer from column H, which is the 4th column of the range: =INDEX(E:H,MATCH(E10,E:E,0),4) |
Lookup Function Not in Ascending Order
Precisely what I was looking for. Thank you so much! I am extremely happy
now; No more repetitive data entry! But regardless, on your INDEX/MATCH question, change the INDEX range to the whole range and put in a column reference. So this checks column E, then returns an answer from column H, which is the 4th column of the range: =INDEX(E:H,MATCH(E10,E:E,0),4) |
Lookup Function Not in Ascending Order
Actually, I spoke a little too soon. =INDEX(E:H,MATCH(E10,E:E,0),4)
returns the correct answer if it is already listed above, but not if it is below...How can I get it to return an answer whether it's listed above or below? Thank you again "Spiky" wrote: If I am to input "Milan" into E?, I want H? to be pre-populated. But I'm a bit unclear with this =INDEX(return_column,MATCH(lookup,sought_column,0) ) so... =Index(H:H,MATCH(E?,E:E,0)) does not seem to be correct. Thank you for all your help, this will really help me out at work, but I don't always have a lot of time to dedicate to experiment. Well, if I'm reading you right and this formula goes in the cells in H, it won't work easily since that is potentially circular or at least messy. You'd have to have all your locations actually typed into the first, say, 100 rows, then subsequent rows could access those first 100 with whatever lookup will work. And if you come up with a new city sometime, no lookup will work at all since it is the first time you typed it in, there's nothing to look up. You might want to have the city/country list somewhere else to reference. Perhaps put the location data far off to the right in BA:BB. Then your original idea of a VLOOKUP would work. But regardless, on your INDEX/MATCH question, change the INDEX range to the whole range and put in a column reference. So this checks column E, then returns an answer from column H, which is the 4th column of the range: =INDEX(E:H,MATCH(E10,E:E,0),4) |
All times are GMT +1. The time now is 01:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com