If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
LookUp Function
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? |
#2
|
|||
|
|||
LookUp Function
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. -- Biff Microsoft Excel MVP "KAL64" wrote in message ... 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? |
#3
|
|||
|
|||
LookUp Function
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 |
#4
|
|||
|
|||
LookUp Function
Biff,
You assume that all search values appear in Sheet2. I don't. Regards, Bernd |
#5
|
|||
|
|||
LookUp Function
You assume that all search values appear in Sheet2.
Yep. That's what this means: Making a few assumptions... I'm also assuming that the range(s) noted by the OP are the REAL ranges. -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Biff, You assume that all search values appear in Sheet2. I don't. Regards, Bernd |
#6
|
|||
|
|||
LookUp Function
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 he http://msdn2.microsoft.com/en-us/library/aa730921.aspx -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... 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 |
#7
|
|||
|
|||
LookUp Function
Hi Biff,
I was also a bit surprised. I uploaded my test file: http://www.sulprobil.com/software/20...up_Matrix.xlsx [open and use at your own risk ...] The FastExcel tab shows the comparison... Regards, Bernd |
Thread Tools | |
Display Modes | |
|
|