Multiple combined formulas ?

[REB]

I am not sure what combinations of formula’s are needed to lookup an item,
evaluate multiple results and list the result.

Here is a sample scenario:

ID Item Result
1234 Degree 30
1234 Degree 15
1234 Method Hand
456 Style Green
789 Degree 30
1023 Degree 15
1023 Degree 10
1023 Method Foot

I need to be able to lookup the ID number e.g. “1234†in column A, evaluate
all the item responses for 1234 that match, e.g. “Degree†in column B and
then evaluate the results for all the items called “Degree†for ID 1234 and
then if the Result is e.g. 30, record 30 in the cell.

Hope I have explained it well enough. Any help would be appreciated.

Thank you!

 

[T. Valko]

Hope I have explained it well enough.

Not sure!

Is this what you want:

You want to lookup ID 1234 and item Degree and see if there is a result of
30, if so, return 30?

 

[REB]

Biff,

Yes, you are correct. In some cases the resutl of 30 may not be there I
would need it to return the answer that is there or maybe a "no" or some
indication that it did not find the correct answer, if possible.
Thanks for the help.
--
REB

Hope I have explained it well enough.

Not sure!

Is this what you want:

You want to lookup ID 1234 and item Degree and see if there is a result of
30, if so, return 30?

 

[T. Valko]

In some cases the resutl of 30 may not be there

I would need it to return the answer that is there

Return the answer that is *where* ?

If you're specifically looking for 1234 Degree 30 and there is no 30 what
result do you want?


--
Biff
Microsoft Excel MVP

Biff,

Yes, you are correct. In some cases the resutl of 30 may not be there I
would need it to return the answer that is there or maybe a "no" or some
indication that it did not find the correct answer, if possible.
Thanks for the help.

 

[REB]

Return the answer that is *where* ? A: The answer in column C that
contains the responses for "degree".....looking at the original sample, if I
was looking for # "1023", "Degree", "30" in the two "Degree" answers listed
and 30 is not there, if it could report the highest number found for "1023",
"Degree" (e.g. 15) that would be good, if not, "None" or "False" or some text
answer that would work in the formula would be okay.

Thanks!!!

 

[T. Valko]

See if this does what you want:

E2 = some ID number = 1023
F2 = some item = degree

Entered as an array** :

=MAX(IF((A2:A9=E2)*(B2:B9=F2),C2:C9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[REB]

Wow, I think that will work! Will go back and try it in my real spreadsheet!!!

Will MAX only work with numbers? Going back your last response and my
example what if E2 was 1023, F2 = Method. Is there a way to get it to list
"foot" as a text response?

Thanks!!!!!

 

[T. Valko]

Will MAX only work with numbers?

Yes, that is correct

what if E2 was 1023, F2 = Method.

Try this array formula** :

E2 = some ID number = 1023
F2 = some item = method

=INDEX(C2:C9,MATC(1,(A2:A9=E2)*(B2:B9=F2),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[REB]

Great! It is working! Thank you for your time and assistance in teaching me
about these array formulas!!

 

[REB]

It worked on some but.. I need to use a "range of cells" now in another
worksheet in the formula you gave me. (I know how to select a range from the
other worksheet that I use in other formulas all the time.) The formula is
giving me a "blank" cell with no answer.

Don't know if it is how I selected the range or in this case there are
multiple E2's with "1023" and multiple F2 with "methods" and there are
multiple "foot" responses in "C" and some "hand" response in "C" for 1023 &
method. Trying to figure out which of those variables is causing the "blank"?

Thanks

 

[T. Valko]

I think I need a more detailed explanation of what you're trying to do. Tell
me using your real setup and real data. Tell me exactly where the data is.
Don't use general descriptions like the data is on sheet2. Where *exactly*
on sheet2?

In other words, I need *very specific details* on what you're wanting to do.