• So, you are saying that SQL Server determined --

    the cost of finding a few records (using second index) + lookup for other fields

    was greater than ---

    the cost of searching through thousands of records (using first index) to find the few matching that where clause but without a need to do a lookup for the additional fields needed

    and that is why the first index was chosen.

    I always thought that the index with the greatest number of equality matches (in the order of the columns in the index without any gaps) was always selected. Only when there were range matches or two or more indexes could satisfy the equality matches would it take into account things like lookups.