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.

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