Vlookup for multiple selections
 

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

Vlookup for multiple selections
 

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-Pro


"Billing Goddess" wrote:

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

Vlookup for multiple selections
 

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

HTH
--
AP

"Billing Goddess" a écrit dans le
message de ...
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

Vlookup for multiple selections
 

=INDEX(L1:L100,MATCH(40066&80964,K1:K100&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)

"Billing Goddess" wrote in
message ...
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

Vlookup for multiple selections
 

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,
Paula


"Bob Phillips" wrote:

=INDEX(L1:L100,MATCH(40066&80964,K1:K100&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)

"Billing Goddess" wrote in
message ...
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

Vlookup for multiple selections
 

Ardus Petus wrote...
=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.

Vlookup for multiple selections
 

Billing Goddess wrote...
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
....

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.

Vlookup for multiple selections
 

Bob Phillips wrote...
=INDEX(L1:L100,MATCH(40066&80964,K1:K100&Q1:Q100, 0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
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))