using vlookup with multiple lookup values

Posted by dan on September 24, 2001 10:46 AM

I am trying to use a vlookup to combine values it finds. =hlookup("value",a1:g5,2)
What it is searching through is value 15, value 25, value technology 5.

Does anyone know of a way to add the values it finds for all three versions of value without using an advanced filter or is h/v lookup limited to such?

Thank you

Posted by Aladin Akyurek on September 24, 2001 11:07 AM

Dan,

Care to post 10 rows data along with the expected result(s)? In order to do that, activate an empty cell, type the =-sign, then select 10 rows of data including column headings if any, and hit control+shift+enter. Go the formula bar, select the formula and hit F9. Copy what you see and paste it in the follow-up.

Aladin

============

Posted by dan on September 24, 2001 11:27 AM

=-{"CR EQ AC 100","CR EQ AC 50","CR EQ AC 75","CR EQ ACCT","CS W PVECA85","CS W PVENCAP","CS WP EGP 50","CS WP EGP 75";9,6,14,3,6,1,5,2;12,2,3,2,2,9,2,2}

I am trying to find a way to just search for "CR EQ AC" (I left in an example of another fund) and sum the first row below, then sum the second, and I will continue this for many more. I will end up searching through 48 columns and 45 rows of data below each fund name. I have been trying if/count/find combinations but with no sucess.

results desired row 1 =32; row 2 =19

Thank you again

Posted by Aladin Akyurek on September 24, 2001 11:51 AM

Dan --

Thanks for the clearly stated specs.

I'll assume your sample to be in A1:H3.

In J1 enter: CR EQ AC [ the criterion ]

In J2 enter: =SUMPRODUCT((ISNUMBER(SEARCH($J$1,$A$1:$H$1)))*(A2:H2))

Copy down this as far as needed.

Aladin

Posted by dan on September 24, 2001 12:36 PM

Aladin that worked increadibly well. I have been using cheats around this same problem for months adding vlookups together. Thank you very much for the excellent formula.