Lookup Function
I'm using a lookup_value,lookup_vector,result_vector function as an array
formula to lookup first and last names in a two column array table and locate their assigned groups. The table is sorted ascending to descending by last name. The reason for using an array formula is that if I have more than one person with the same last name I need it to identify which person with that same last name. This works on a small test area to ensure the formula was written correctly, but when I apply it to the entire table it matches all of the wrong information. So if I want it to find JOE BROWN its finding MIGUEL ZABOS and the resulting vector (in this case, manager assigned to MIGUEL) isn't the correct result. Any ideas on what I'm doing wrong would be greatly appreciated! Here's a sample formula I'm using: {=LOOKUP(B2:C2,Sheet1!$A$2:$B$92,Sheet1!$C$2:$C$92 )} It should find MARK (B2) BAKER (C2) and result in BAKER, MARK, but all it returns is the number zero. |
Lookup Function
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in 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 only use the whole column in xl2007. 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)) Aequo79 wrote: I'm using a lookup_value,lookup_vector,result_vector function as an array formula to lookup first and last names in a two column array table and locate their assigned groups. The table is sorted ascending to descending by last name. The reason for using an array formula is that if I have more than one person with the same last name I need it to identify which person with that same last name. This works on a small test area to ensure the formula was written correctly, but when I apply it to the entire table it matches all of the wrong information. So if I want it to find JOE BROWN its finding MIGUEL ZABOS and the resulting vector (in this case, manager assigned to MIGUEL) isn't the correct result. Any ideas on what I'm doing wrong would be greatly appreciated! Here's a sample formula I'm using: {=LOOKUP(B2:C2,Sheet1!$A$2:$B$92,Sheet1!$C$2:$C$92 )} It should find MARK (B2) BAKER (C2) and result in BAKER, MARK, but all it returns is the number zero. -- Dave Peterson |
All times are GMT +1. The time now is 12:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com