Excel Unique-Filter issue

[Kennelbloke]

Hi Folks. Just trying to get a bit Excel happening, not my forte, but can't work out why I'm having this issue
So 3 sheets. Entries/Breeds/Colours
I'm using the Unqiue/Filter combination to get the base of the data I want. With the Finction below in F1, I get the following


What I'm trying to do is retrieve a corresponding number from column F based on the criteria of BreedID (9 in this) case and the selected colour.

D2's function is =INDEX($F:$F;MATCH(1;(C2=$H:$H)*(B2=$G:$G);0)) But it's not finding, in this case, 43 in the F column. What have I got wrong please?

 

[Kennelbloke]

I believe that this maybe something to do with Column G. On the Colours sheet, this value is numeric (right alignment of cell) and on the array returned it looks to text even though the column is formatted as a number. If that is the case then how would I fix this?

UPDATE!! In case anyone else comes looking for a similar solution.
I fixed this by referring D2 back to the Colours Sheets =INDEX(Colours!$A:$A;MATCH(1;(C2=Colours!$C:$C)*(B2=Colours!$B:$B);0))

 

[SkipVought]

Your question and requirements ought to be clear, concise and complete with respect to each item required.

However, you failed to include any information regarding the data on your Colours sheet.

In most instances, a picture pretty much worthless but a sample workbook would be most helpful if you're looking for help.

BTW, using tables without Headers ought to be a capital offense, punishable by 40 lashes with a wet noodle.

D2: =SUMPRODUCT(($G:$G=B2)*($H:$H=C2)*($F:$F))

You can't use INDEX & MATCH if you have multiple criteria.

But the question I'd pose is, why are you putting that headerless table on your sheet. The data is on the Colour sheet! But you failed to tell us anything about the data that you needlessly referenced. In so doing, you ham stringed your potential Tek-Tippers and thus procured a less than optimal solution. But I'll take a guess...

D2: =SUMPRODUCT((Colours!$B:$B=B2)*(Colours!$C:$C=C2)*(Colours!$A:$A))

Hope this helps.

 

[SkipVought]

This is a better solution that can accommodate TEXT or NUMERIC lookups, although one ought never mix TEXT & NUMBERS in the same table column.

I dummied up some data, but I used TEXT rather than DIGITS in the ColourID COLUMN (Colours A) to demonstrate the multiple lookup criteria method via SUMPRODUCT. The only caveat is that if any given criteria-set refers to multiple rows, you will get an incorrect result.