Array Lookup Help

Hi,

I've been trying to find a good formula for the following task:

Within an excel table, I have two arrays:

I want a formula to search "Bob" and return "1", something that takes the coordinates of Bob within the first array and outputs the corresponding coordinate text within a separate array.

Thanks!

Druz09 said:

I want a formula to search "Bob" and return "1",

Click to expand...

If Criteria in A9 (Bob) then formula can be

navic said:

If Criteria in A9 (Bob) then formula can be

Code:

=SUMPRODUCT((A9=$A$1:$C$3)*$A$5:$C$7)
or
=SUMIF(A1:C3,A9,A5:C7)

Click to expand...

Thanks! This works perfectly.