Lookup Help... Again.

[Doug S]

You guys were so great last time here is another one for you...

I have two 'scorecards' with an id number, name, and score. Both are sorted
by score in descending order. I would like to generate cumulative scorecard
with both scores and a summary score but when I try and look up the score up
based upon the ID I am getting incorrect results. There is a match for every
name so it isn't a a null error. The formula I'm using is as follows...

=IF(ISNA(LOOKUP($E43,'Phase I Scorecard'!$E$13:$E43,'Phase I
Scorecard'!$H$13:$H43)),"",LOOKUP($E43,'Phase I Scorecard'!$E$13:$E43,'Phase
I Scorecard'!$H$13:$H43))

 

[Pete_UK]

I would suggest that you try this:

=IF(ISNA(VLOOKUP($E43,'Phase I Scorecard'!$E$13:$H43,4,0)),"",VLOOKUP
($E43,'Phase I Scorecard'!$E$13:$H43,4,0))

The 0 (or it could be FALSE) at the end of the VLOOKUP indicates that
you are looking for an exact match, so the table can be in any order.
The 4 indicates that you want to get data from the 4th column of the
table if there is a match in the first column. VLOOKUP is more useful
than LOOKUP, I find.

Hope this helps.

Pete

 

[Paul C]

I would try forcing an exact match lookup as follows:

=IF(ISerror(Match($E43,'Phase I
Scorecard'!$E$13:$E43,0),"",VLOOKUP($E43,'Phase I
Scorecard'!$E$13:$H43,4,false))

The VLOOKUP(Lookupvalue,Range,Column,Type) formula can sometime be better
that the simple LOOKUP. The 4 for column sets the results column as the 4th
coulmn in the definded range (H in this case), and the False type forces an
exact match. The 0 type on the match function also forces an exact match.
In both cases it no longer matters if the data is sorted. One caution, only
the first match will be found. If there are duplicate values in E13:E43 you
will not be able to use a simple formula to return items for the second
match. I would assume that the ID number would be unique for each data line,
but you should confirm this.

 

[Doug S]

Thank you! Actually both work like a charm.