LookUp Function

[KAL64]

I have values (V1) in one column (Sheet1!A1:A200) that I need to search for
in multiple columns in another worksheet (Sheet2!B thru Sheet2!x). Once V1
is located I then need to return the value (V2) from Sheet2!A1:A200 assined
to V1. Is this possible and if so how would i go about it?

 

[T. Valko]

Making a few assumptions...

Try this array formula** :

=INDEX(Sheet2!A$1:A$200,MAX(IF(Sheet2!B$1:X$200=A1,ROW(Sheet2!B$1:X$200))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Bernd P]

Hello,

Just for the fun of it:

Slightly faster (about 10%) is this array formula:
=INDEX(Sheet2!A$1:A$200,MATCH(1,SIGN(MMULT(--(Sheet2!$B$1:$X
$200=Sheet1!A1),TRANSPOSE(--(Sheet2!$B$1:$X$1=Sheet2!$B$1:$X$1)))),0))

With the evaluated Transpose() function this formula can be entered
normally (not as array...):
=INDEX(Sheet2!A$1:A$200,MATCH(1,SIGN(MMULT(--(Sheet2!$B$1:$X
$200=Sheet1!A1),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})),0))
But now it's slower (just about 1% faster).

In a company I would use a VBA solution.

Regards,
Bernd

 

[Bernd P]

Biff,

You assume that all search values appear in Sheet2.

I don't.

Regards,
Bernd

 

[T. Valko]

You assume that all search values appear in Sheet2.

Yep. That's what this means:

I'm also assuming that the range(s) noted by the OP are the REAL ranges.

 

[T. Valko]

Slightly faster (about 10%) is this array formula:

=INDEX(Sheet2!A$1:A$200,MATCH(1,SIGN(MMULT(--(Sheet2!$B$1:$X

$200=Sheet1!A1),TRANSPOSE(--(Sheet2!$B$1:$X$1=Sheet2!$B$1:$X$1)))),0))

Hmmm....

In my tests that formula is slightly slower:

INDEX/MAX
INDEX/MATCH

0.00384, 0.00371, 0.00368, 0.00369, 0.00380
0.00411, 0.00409, 0.00410, 0.00406, 0.00409

Using the timer routines found here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

 

[Bernd P]

Hi Biff,

I was also a bit surprised.

I uploaded my test file:
www.sulprobil.com/software/20100213_PB_01_Lookup_Matrix.xlsx
[open and use at your own risk ...]

The FastExcel tab shows the comparison...

Regards,
Bernd