Lookup

[scott]

I am using XL2000 and haven't been able how to create this lookup:

I want to check if each cell in Range 1 E9:E25 is in the lookup range
R9:R25. If it is I want to count the number of times "A" exists in F9:F25.
There could be more than on instance of the Range1 value in the range.

I figured I couldn't use a regular array (E9:E25=F9:F25) because the
matches will not be in the same row

I have tried to use match, but had problems because of multiple
occurences in the lookup range.

So I tried an array formula with Vlookup , but this total give me every
A, regardless of whether is the lookup value is in R9:R25
=SUM(IF(ISERROR((VLOOKUP(E9:E25,R9:R25,1,0))="FALSE")*(F9:F25="A"),1,0))

If someone could help get me started, or at least help with doing a true
false lookup in an array.

Thanks,

Scott

 

[Guest]

Try using the COUNTIF function

=COUNTIF($R$9:$R$25,E9

Note that the range is an Absolute Reference, so when you copy the formula it won't change the range

Good Luck
Mark Graesse
(e-mail address removed)

----- scott wrote: ----

I am using XL2000 and haven't been able how to create this lookup

I want to check if each cell in Range 1 E9:E25 is in the lookup range
R9:R25. If it is I want to count the number of times "A" exists in F9:F25
There could be more than on instance of the Range1 value in the range

I figured I couldn't use a regular array (E9:E25=F9:F25) because the
matches will not be in the same ro

I have tried to use match, but had problems because of multiple
occurences in the lookup range

So I tried an array formula with Vlookup , but this total give me every
A, regardless of whether is the lookup value is in R9:R2
=SUM(IF(ISERROR((VLOOKUP(E9:E25,R9:R25,1,0))="FALSE")*(F9:F25="A"),1,0)

If someone could help get me started, or at least help with doing a true
false lookup in an array

Thanks

Scot

 

[Peo Sjoblom]

One way


=IF(SUMPRODUCT(--COUNTIF(E9:E25,R9:R25))=COUNTA(E9:E25),COUNTIF(F9:F25,"A"),
"No")

however what if there are only 6 unique values in E9:E25 but they all are in
R9:R25?
Maybe you want all values in E9:E25 are unique and all values must be in
R9:R25

=IF(SUMPRODUCT(--(COUNTIF(E9:E25,R9:R25)=1))=COUNTA(E9:E25),COUNTIF(F9:F25,"
A"),"No")

Is the A part of a string, in that case

=IF(SUMPRODUCT(--(COUNTIF(E9:E25,R9:R25)=1))=COUNTA(E9:E25),COUNTIF(F9:F25,"
*A*"),"No")

 

[scott]

Thank you both for your quick replies:
I never would have thought of countif when thinking of a lookup. I used
Mark's suggestion to combine everything into an array formula, but I
kept getting the count of all A's using Peo's formulas.

I will have to do some testing but this appears to work
=SUM(IF((COUNTIF($R$9:$R$25,E9:E25)>0)*(F9:F25="A"),1,0))

Thanks again

Scott