How do I select a value from a table based on 2 conditional refs?

[Guest]

I'm trying to select a value from a table based on 2 variable references.

If been looking at using a VLOOKUP function to do this but because there are
2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP
with a MATCH function incorporated might be an option as the entries in each
column are not unique.

To give you an example of what I am trying to achieve I done the table below.

Basically if F1=9 & G2=2 then select C1 (*)

A B C D F G
1 9 3 * % 9 2
2 9 2 % &
3 9 1 # *
4 7 3 * %

Thanks

 

[Guest]

Hi

For your example try:
=SUMPRODUCT((A1:A4=F1)*(B1:B4=G2)*(C1:C4))
I'm presuming (by the way) that your example is wrong, as I would have
expected it to return %.
When using SUMPRODUCT, you can't use full column ranges (A:A) and the ranges
used must be the same size.

Hope this helps.
Andy.

 

[Guest]

Thanks for your response Andy. Sorry, my example did have a mistake in it.

My actual data contains dates which I should have mentioned in my original
email. I don't think that this function would work because of that. I need
to find a way to search for a date which matches with a value (eg: 50) which
then okays the further selection of another specified date in the same table.

Thanks for your feedback. Any further thoughts would be appreciated.

Puzzled Percy

 

[Guest]

Hi

There is no reason why this format of formula will not work with dates - as
you want. If you post some more accurate data I'll have a go!

Andy.

 

[Bob Phillips]

=INDEX(C1:C4,MATCH(1,(A1:A4=F1)*(B1:B4=G1),0))

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


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Hi Andy,

I should have tried your fomula first before doubting you! (Won't do that
again) It works great for what I want it to do given the way that Excel
works out dates.

You are fantastic

Regards,

Puzzled Percy

 

[Guest]

Thanks Bob,

Your formula works great too! This was more what I had in mind when I first
posted as I will be able to use text with it in a different scenario. You
are FANTASTIC

Regards,

Puzzled Percy