Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
I'm trying to read employee's records, and I have lots of Bobs and others,
but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
One way:
Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
It's not working.
And the information being pulled is alpha-numeric, and the names are listed only once. Thanks for your help. "T. Valko" wrote: One way: Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
What does "it's not working mean" ?
Getting an error? Incorrect result? If you have: A1 = Bob B1 = Smith And on sheet Addresses-Main Data Sheet: Column A = first names Column B = last names That formula will work. Did you enter the formula as an array? ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Claudio Funes" wrote in message ... It's not working. And the information being pulled is alpha-numeric, and the names are listed only once. Thanks for your help. "T. Valko" wrote: One way: Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
It gives me a #N/A result. And yes, I entered it as an array.
"T. Valko" wrote: What does "it's not working mean" ? Getting an error? Incorrect result? If you have: This is correct. A1 = Bob B1 = Smith And on sheet Addresses-Main Data Sheet: Column A = first names Column B = last names That formula will work. Did you enter the formula as an array? ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Claudio Funes" wrote in message ... It's not working. And the information being pulled is alpha-numeric, and the names are listed only once. Thanks for your help. "T. Valko" wrote: One way: Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Here's a small sample file that demonstrates this:
sample_lookup1.xls 14kb http://cjoint.com/?geh0BIqI5V As you'll see, the formula does work. If you're having problems I would check the data. Your lookup values may have unseen characters like leading/trailing spaces or other unseen characters that is causing them not to match with the lookup table. Or, the names on the lookup table may have the unseen characters. Biff "Claudio Funes" wrote in message ... It gives me a #N/A result. And yes, I entered it as an array. "T. Valko" wrote: What does "it's not working mean" ? Getting an error? Incorrect result? If you have: This is correct. A1 = Bob B1 = Smith And on sheet Addresses-Main Data Sheet: Column A = first names Column B = last names That formula will work. Did you enter the formula as an array? ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Claudio Funes" wrote in message ... It's not working. And the information being pulled is alpha-numeric, and the names are listed only once. Thanks for your help. "T. Valko" wrote: One way: Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
It works great, thank you very much.
"T. Valko" wrote: Here's a small sample file that demonstrates this: sample_lookup1.xls 14kb http://cjoint.com/?geh0BIqI5V As you'll see, the formula does work. If you're having problems I would check the data. Your lookup values may have unseen characters like leading/trailing spaces or other unseen characters that is causing them not to match with the lookup table. Or, the names on the lookup table may have the unseen characters. Biff "Claudio Funes" wrote in message ... It gives me a #N/A result. And yes, I entered it as an array. "T. Valko" wrote: What does "it's not working mean" ? Getting an error? Incorrect result? If you have: This is correct. A1 = Bob B1 = Smith And on sheet Addresses-Main Data Sheet: Column A = first names Column B = last names That formula will work. Did you enter the formula as an array? ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Claudio Funes" wrote in message ... It's not working. And the information being pulled is alpha-numeric, and the names are listed only once. Thanks for your help. "T. Valko" wrote: One way: Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
You're welcome. Thanks for the feedback!
Biff "Claudio Funes" wrote in message ... It works great, thank you very much. "T. Valko" wrote: Here's a small sample file that demonstrates this: sample_lookup1.xls 14kb http://cjoint.com/?geh0BIqI5V As you'll see, the formula does work. If you're having problems I would check the data. Your lookup values may have unseen characters like leading/trailing spaces or other unseen characters that is causing them not to match with the lookup table. Or, the names on the lookup table may have the unseen characters. Biff "Claudio Funes" wrote in message ... It gives me a #N/A result. And yes, I entered it as an array. "T. Valko" wrote: What does "it's not working mean" ? Getting an error? Incorrect result? If you have: This is correct. A1 = Bob B1 = Smith And on sheet Addresses-Main Data Sheet: Column A = first names Column B = last names That formula will work. Did you enter the formula as an array? ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Claudio Funes" wrote in message ... It's not working. And the information being pulled is alpha-numeric, and the names are listed only once. Thanks for your help. "T. Valko" wrote: One way: Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Just one last question, what does the 3 in your formula represent?
In my VLOOKUP string it represented the column from which I wanted to pull data from, in yours I'm not sure. Thanks "T. Valko" wrote: Here's a small sample file that demonstrates this: sample_lookup1.xls 14kb http://cjoint.com/?geh0BIqI5V As you'll see, the formula does work. If you're having problems I would check the data. Your lookup values may have unseen characters like leading/trailing spaces or other unseen characters that is causing them not to match with the lookup table. Or, the names on the lookup table may have the unseen characters. Biff "Claudio Funes" wrote in message ... It gives me a #N/A result. And yes, I entered it as an array. "T. Valko" wrote: What does "it's not working mean" ? Getting an error? Incorrect result? If you have: This is correct. A1 = Bob B1 = Smith And on sheet Addresses-Main Data Sheet: Column A = first names Column B = last names That formula will work. Did you enter the formula as an array? ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Claudio Funes" wrote in message ... It's not working. And the information being pulled is alpha-numeric, and the names are listed only once. Thanks for your help. "T. Valko" wrote: One way: Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
It means the same thing, the column number. I used that particular formula
specifically so that, if needed, you could use it to pull other data from the same table by simply changing the column number. Biff "Claudio Funes" wrote in message ... Just one last question, what does the 3 in your formula represent? In my VLOOKUP string it represented the column from which I wanted to pull data from, in yours I'm not sure. Thanks "T. Valko" wrote: Here's a small sample file that demonstrates this: sample_lookup1.xls 14kb http://cjoint.com/?geh0BIqI5V As you'll see, the formula does work. If you're having problems I would check the data. Your lookup values may have unseen characters like leading/trailing spaces or other unseen characters that is causing them not to match with the lookup table. Or, the names on the lookup table may have the unseen characters. Biff "Claudio Funes" wrote in message ... It gives me a #N/A result. And yes, I entered it as an array. "T. Valko" wrote: What does "it's not working mean" ? Getting an error? Incorrect result? If you have: This is correct. A1 = Bob B1 = Smith And on sheet Addresses-Main Data Sheet: Column A = first names Column B = last names That formula will work. Did you enter the formula as an array? ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Claudio Funes" wrote in message ... It's not working. And the information being pulled is alpha-numeric, and the names are listed only once. Thanks for your help. "T. Valko" wrote: One way: Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Yes, I had figured it out, but thank you very much, you've been a great help.
Regards, Claudio "T. Valko" wrote: It means the same thing, the column number. I used that particular formula specifically so that, if needed, you could use it to pull other data from the same table by simply changing the column number. Biff "Claudio Funes" wrote in message ... Just one last question, what does the 3 in your formula represent? In my VLOOKUP string it represented the column from which I wanted to pull data from, in yours I'm not sure. Thanks "T. Valko" wrote: Here's a small sample file that demonstrates this: sample_lookup1.xls 14kb http://cjoint.com/?geh0BIqI5V As you'll see, the formula does work. If you're having problems I would check the data. Your lookup values may have unseen characters like leading/trailing spaces or other unseen characters that is causing them not to match with the lookup table. Or, the names on the lookup table may have the unseen characters. Biff "Claudio Funes" wrote in message ... It gives me a #N/A result. And yes, I entered it as an array. "T. Valko" wrote: What does "it's not working mean" ? Getting an error? Incorrect result? If you have: This is correct. A1 = Bob B1 = Smith And on sheet Addresses-Main Data Sheet: Column A = first names Column B = last names That formula will work. Did you enter the formula as an array? ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Claudio Funes" wrote in message ... It's not working. And the information being pulled is alpha-numeric, and the names are listed only once. Thanks for your help. "T. Valko" wrote: One way: Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
You're welcome!
Biff "Claudio Funes" wrote in message ... Yes, I had figured it out, but thank you very much, you've been a great help. Regards, Claudio "T. Valko" wrote: It means the same thing, the column number. I used that particular formula specifically so that, if needed, you could use it to pull other data from the same table by simply changing the column number. Biff "Claudio Funes" wrote in message ... Just one last question, what does the 3 in your formula represent? In my VLOOKUP string it represented the column from which I wanted to pull data from, in yours I'm not sure. Thanks "T. Valko" wrote: Here's a small sample file that demonstrates this: sample_lookup1.xls 14kb http://cjoint.com/?geh0BIqI5V As you'll see, the formula does work. If you're having problems I would check the data. Your lookup values may have unseen characters like leading/trailing spaces or other unseen characters that is causing them not to match with the lookup table. Or, the names on the lookup table may have the unseen characters. Biff "Claudio Funes" wrote in message ... It gives me a #N/A result. And yes, I entered it as an array. "T. Valko" wrote: What does "it's not working mean" ? Getting an error? Incorrect result? If you have: This is correct. A1 = Bob B1 = Smith And on sheet Addresses-Main Data Sheet: Column A = first names Column B = last names That formula will work. Did you enter the formula as an array? ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Claudio Funes" wrote in message ... It's not working. And the information being pulled is alpha-numeric, and the names are listed only once. Thanks for your help. "T. Valko" wrote: One way: Try this array formula** : =INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If the data to be returned is numeric and the names (first last) are listed only once in the lookup sheet we can use a less complicated non-array formula. Biff "Claudio Funes" wrote in message ... I'm trying to read employee's records, and I have lots of Bobs and others, but different last names on the following column. The question is: how do I make it read the next column to match the last name to extract the information for that row? I can't just use the second column because there some same last names. This is what I have right now, where $A1 is the first name, and naturally the last name is B1. =VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0) Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Lookup/Double Lookup | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) |