LookUp Function

  • Thread starter Thread starter KAL64
  • Start date Start date
K

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?
 
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.
 
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
 
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.
 
Slightly faster (about 10%) is this array formula:
=INDEX(Sheet2!A$1:A$200,MATCH(1,SIGN(MMULT(--(Sheet2!$B$1:$X
Click to expand...
$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
 
Back
Top