software

I hope someone can help!

I have the following data

col K col L col Q

40066 $2000.00 80962
40066 $10,000.00 80963
40066 $5.00 80964
40067 $75.00 80966
40067 $20.00 80900
40068 $2200.00 80901

I need the value in col L if col k is 40066 and col q is 80964. All values
are always within one row, meaning that if 40066 and 80964 are found, the $$
will be in that row also. Does this make sense??

--
Many Thanks,
Paula

If the Col_L values will always be numeric
AND there will only be ONE Col_L value for
any combination of Col_K and Col_Q,
then try this:

A1: 40066
A2: 80964

B1: =SUMPRODUCT((K1:K10=A1)*(Q1:Q10=A2)*L1:L10)
In this case the formula returns 5.

Change range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Billing Goddessquot; wrote:

gt; I hope someone can help!
gt;
gt; I have the following data
gt;
gt; col K col L col Q
gt;
gt; 40066 $2000.00 80962
gt; 40066 $10,000.00 80963
gt; 40066 $5.00 80964
gt; 40067 $75.00 80966
gt; 40067 $20.00 80900
gt; 40068 $2200.00 80901
gt;
gt; I need the value in col L if col k is 40066 and col q is 80964. All values
gt; are always within one row, meaning that if 40066 and 80964 are found, the $$
gt; will be in that row also. Does this make sense??
gt;
gt; --
gt; Many Thanks,
gt; Paula

=SOMMEPROD((K2:K7=40066)*(Q2:Q7=80964);,L2:L7)

HTH
--
AP

quot;Billing Goddessquot; gt; a écrit dans le
message de ...
gt; I hope someone can help!
gt;
gt; I have the following data
gt;
gt; col K col L col Q
gt;
gt; 40066 $2000.00 80962
gt; 40066 $10,000.00 80963
gt; 40066 $5.00 80964
gt; 40067 $75.00 80966
gt; 40067 $20.00 80900
gt; 40068 $2200.00 80901
gt;
gt; I need the value in col L if col k is 40066 and col q is 80964. All values
gt; are always within one row, meaning that if 40066 and 80964 are found, the
$$
gt; will be in that row also. Does this make sense??
gt;
gt; --
gt; Many Thanks,
gt; Paula
=INDEX(L1:L100,MATCH(40066amp;80964,K1:K100amp;Q1:Q100,0 ))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Billing Goddessquot; gt; wrote in
message ...
gt; I hope someone can help!
gt;
gt; I have the following data
gt;
gt; col K col L col Q
gt;
gt; 40066 $2000.00 80962
gt; 40066 $10,000.00 80963
gt; 40066 $5.00 80964
gt; 40067 $75.00 80966
gt; 40067 $20.00 80900
gt; 40068 $2200.00 80901
gt;
gt; I need the value in col L if col k is 40066 and col q is 80964. All values
gt; are always within one row, meaning that if 40066 and 80964 are found, the
$$
gt; will be in that row also. Does this make sense??
gt;
gt; --
gt; Many Thanks,
gt; Paula
Thanks, but I had to use vlookup in order for this to work properly.. but I
was able to use the index formula for another application! Thanks

--
Many Thanks,
Paulaquot;Bob Phillipsquot; wrote:

gt; =INDEX(L1:L100,MATCH(40066amp;80964,K1:K100amp;Q1:Q100,0 ))
gt;
gt; which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt; just Enter.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Billing Goddessquot; gt; wrote in
gt; message ...
gt; gt; I hope someone can help!
gt; gt;
gt; gt; I have the following data
gt; gt;
gt; gt; col K col L col Q
gt; gt;
gt; gt; 40066 $2000.00 80962
gt; gt; 40066 $10,000.00 80963
gt; gt; 40066 $5.00 80964
gt; gt; 40067 $75.00 80966
gt; gt; 40067 $20.00 80900
gt; gt; 40068 $2200.00 80901
gt; gt;
gt; gt; I need the value in col L if col k is 40066 and col q is 80964. All values
gt; gt; are always within one row, meaning that if 40066 and 80964 are found, the
gt; $$
gt; gt; will be in that row also. Does this make sense??
gt; gt;
gt; gt; --
gt; gt; Many Thanks,
gt; gt; Paula
gt;
gt;
gt;

Ardus Petus wrote...
gt;=SOMMEPROD((K2:K7=40066)*(Q2:Q7=80964);,L2:L7)

Unhelpful to use non-English function names in English language
newsgroups without mentioning that you don't know the English function
name.Billing Goddess wrote...
gt;Thanks, but I had to use vlookup in order for this to work properly.. but I
gt;was able to use the index formula for another application! Thanks
....

You couldn't have used VLOOKUP to solve the question you posed as you
posed. Did you insert a column to the left of your column L that
contained formulas that concatenated the values in your original
columns K and Q? If not, you may think you've solved this, but it's
more likely your formula happenstantially returns the correct value for
the specific data you're using. As the saying goes, a stopped clock
shows the correct time twice a day.Bob Phillips wrote...
gt;=INDEX(L1:L100,MATCH(40066amp;80964,K1:K100amp;Q1:Q100, 0))
gt;
gt;which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt;just Enter.
....

An alternative that doesn't depend on fixed width fields in columns K
and Q,

=INDEX(L1:L100,MATCH(1,(K1:K100=40066)*(Q1:Q100=80 964),0))