Lookup from a list of numbers

[Dean]

Hi, I currently am using an array formula to match up certain values, if they
match, then I pull a Qty value from that row. I want to be able to have one
of those value types come from a list of numbers, I call Ncodes. To simply
how the data looks I've created a short version.

Type Ord# QtrCR Qty NCode
A B C D E
5 ze4 4501 1Q08 10 1117
6 ze3 4502 1Q08 15 1116
7 ze4 4503 1Q08 15 1117
8 ze6 4504 1Q08 12 1119

If my Ncode list looks like this, and I name the range "Ncode" -
Placed in a separate area of the Spreadsheet, the result should show Qty of
40.
AA
10 1116
11 1117

Below is an example of the array statement that would pull 1117 only, but it
doesn't work to name a range in place of the 1117, such as AA10:AA11, or use
a named range of Ncode. I'm looking at possibly have an Ncode list of 7 to 10
numbers. So I'm wanting a TRUE for that portion of the formula if any one of
the Ncode list causes a match.

{=(SUM((IF(ISERROR(FIND("Q08",Buffer!$C$5:$C$8)),0,1))*(IF(ISNUMBER(Buffer!$B$5:$B$8),1,0))*(IF(ISERROR(FIND(1117,Buffer!$E$5:$E$8)),0,1))*Buffer!$D$5:$D$8)}

Thanks ahead of time for any help. Dean

 

[Bernie Deitrick]

Dean,

Based on your example table:

=SUMPRODUCT(NOT(ISERROR(MATCH(E5:E8,NCode,FALSE)))*D58)

 

[Dean]

Bernie, you are great, I used the Match portion in place of the FIND, it
works wonderful. Thanks very much. Dean