Index Modification

**esupply** · 03-11-2010, 05:24 PM

I seriously dont know what i am doing wrong today but i cannot get these formulas right at all.
I am trying to pull information from sheet by matching it with specific cell information and i cant get it to work please see attached workbook to see what im doing wrong and maybe you can throw a little insight my way. thanks

**martindwilson** · 03-11-2010, 05:40 PM

"Anthony Hoplite space space space..... "
will not match
"Anthony Hoplite"
to get rid of trailing spaces
select sheet 1 column v
then data/text to columns/fixed width/finish
that will give the result of 81.83
alternatively
try
=INDEX(Sheet1!$A$2:$AW$1520,MATCH($B5&"*",Sheet1!$V$2:$V$1520,0),MATCH($S$1,Sheet1!$A$1:$AW$1,0)
or
=INDEX(Sheet1!$A$2:$AW$1520,MATCH($B5,TRIM(Sheet1!$V$2:$V$1520),0),MATCH($S$1,Sheet1!$A$1:$AW$1,0) ) array entered with ctrl+shift+enter

**Paul** · 03-11-2010, 05:41 PM

Hi Esupply,

You can use the following formula in cell S24 on sheet 6WR-3780001:

=SUMIF(Sheet1!V:V,B24,Sheet1!AO:AO)

BUT..... your names in column V of "Sheet1" have spaces after them which won't allow for a match. You can either get rid of all the spaces (using a TRIM function for all cells in that column perhaps) or try this formula in S24:

=SUMIF(Sheet1!V:V,B24&"*",Sheet1!AO:AO)

Note that it will match similar names like "Paul Smith" and "Paul Smithson" or "David Toms" and "David Tomsak".