I have the following formula in my worksheet:

=IF(ISNA(MATCH(B3,FLCR2!$A$3:$A$3786,0)),"Not
Found",VLOOKUP(B3,FLCR2!$A$3:$B$3786,2,0))

What this does is to find a match for cell B3 on the FLCR2 tab within the
range; if the match is not found "Not Found" is returned; if the match is
found Excel looks up the value in the cell to the right of the match and
returns it to the cell containing the formula.

I need to add an additional parameter. Sometimes, the value in B3 matches
several values in column A on FLCR2 tab. Right now, the formula returns the
cell to the right of the first row in which the match occurrs. I would like
it to return the HIGHEST of the values in the cells to the right of any row
it matches. Note, you can't narrow down in advance the range of the rows it
will match=it must be dynamic in the function.

Is this possible, and what additional parameters must I add into the above
function?

Thanks for your help.

Nope.

I mean to put that formula in another cell, but use B3 as the value to match on.

If B3 is Text (numbers treated as text ('123, not ASDF)), you could change the
formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The --B3 will coerce B3 from text to numbers.

This array formula (ctrl-shift-enter, right???)

looks at A3:A3786 of flcr2 and compares it to whats in B3 of the sheet with the
formula (or --b3 to convert it to real numbers).

If there's a match, it uses the numbers in B3:B3786 of fldr2. If there isn't a
match, it ignores that number (it actually retures FALSE for that mismatch).

Then =MAX() takes the largest number.



Tenacity wrote:

I guess you mean to substitute
=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))
for the formula now in B3, which I put in my original post.

This does not work, just returns - meaning, I guess, not found.

One point I did not indicate befo col. B is numeric on FLCR1, but is
text on Sheet 1. Perhaps this is why it's not matching properly.

My formula correctly finds the match, but it places in B3 the value in the
first row it finds rather than the highest value in all the rows it finds.

Perhaps using your MAX array in conjunction with my formula? I just don't
know how to integrate both. Or another possiblity you might suggest.

One other thing. Sorry for the elementary question, but pls. give a brief
explanation of what an array function does in this context.

Thanks for your help.

"Dave Peterson" wrote:

So column B is numeric.

One way:

=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(flcr2!$A$3:$A$3786,b3)=0,"No matches",
MAX(IF(flcr2!$A$3:$A$3786=b3,flcr2!$b$3:$b$3786))

(one cell, and still an array formula)

Tenacity wrote:

I have the following formula in my worksheet:

=IF(ISNA(MATCH(B3,FLCR2!$A$3:$A$3786,0)),"Not
Found",VLOOKUP(B3,FLCR2!$A$3:$B$3786,2,0))

What this does is to find a match for cell B3 on the FLCR2 tab within the
range; if the match is not found "Not Found" is returned; if the match is
found Excel looks up the value in the cell to the right of the match and
returns it to the cell containing the formula.

I need to add an additional parameter. Sometimes, the value in B3 matches
several values in column A on FLCR2 tab. Right now, the formula returns the
cell to the right of the first row in which the match occurrs. I would like
it to return the HIGHEST of the values in the cells to the right of any row
it matches. Note, you can't narrow down in advance the range of the rows it
will match=it must be dynamic in the function.

Is this possible, and what additional parameters must I add into the above
function?

Thanks for your help.

--

Dave Peterson


--

Dave Peterson

I meant what you said, ie, put that formula in another cell, but use B3 as
the value to match on. Sorry. For this discussion, assume the formula is in
C3 on Sheet 1.

Your new formula returns #VALUE! in all the cells into which it is copied,
so unfortunately it's not working.

One problem I see is that your formula seems to say that if B3 equals any
text in FLCR2!$A$3:$A$3786, then it will return the highest numerical value
in all 3783 rows of B on the FLCR tab, ie the highest value found in all the
cells between B3:B3786.

I want the following: if B3 matches, say A14, A18, A23 and A345 on FLCR2,
then I want the formula to return into C3 (the place where is the formula)
the HIGHEST value of B14, B18, B23 and B345 on FLCR2. That is why the MATCH
and VLOOKUP from my formula seem necessary to me, but I want to add to my
formula the concept of MAX or something like it in order to return the
HIGHEST value of these 5 cells and only these 5 cells which matched. And the
next time the formula is used in C4, C5 and so on it might involve the
HIGHEST value of 10 or 15 or 3 cells, ie, it always varies.

Hopefully, I have made it clearer and together we can get closer to the
target. Thanks for your help.

"Dave Peterson" wrote:

Nope.

I mean to put that formula in another cell, but use B3 as the value to match on.

If B3 is Text (numbers treated as text ('123, not ASDF)), you could change the
formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The --B3 will coerce B3 from text to numbers.

This array formula (ctrl-shift-enter, right???)

looks at A3:A3786 of flcr2 and compares it to whats in B3 of the sheet with the
formula (or --b3 to convert it to real numbers).

If there's a match, it uses the numbers in B3:B3786 of fldr2. If there isn't a
match, it ignores that number (it actually retures FALSE for that mismatch).

Then =MAX() takes the largest number.



Tenacity wrote:

I guess you mean to substitute
=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))
for the formula now in B3, which I put in my original post.

This does not work, just returns - meaning, I guess, not found.

One point I did not indicate befo col. B is numeric on FLCR1, but is
text on Sheet 1. Perhaps this is why it's not matching properly.

My formula correctly finds the match, but it places in B3 the value in the
first row it finds rather than the highest value in all the rows it finds.

Perhaps using your MAX array in conjunction with my formula? I just don't
know how to integrate both. Or another possiblity you might suggest.

One other thing. Sorry for the elementary question, but pls. give a brief
explanation of what an array function does in this context.

Thanks for your help.

"Dave Peterson" wrote:

So column B is numeric.

One way:

=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(flcr2!$A$3:$A$3786,b3)=0,"No matches",
MAX(IF(flcr2!$A$3:$A$3786=b3,flcr2!$b$3:$b$3786))

(one cell, and still an array formula)

Tenacity wrote:

I have the following formula in my worksheet:

=IF(ISNA(MATCH(B3,FLCR2!$A$3:$A$3786,0)),"Not
Found",VLOOKUP(B3,FLCR2!$A$3:$B$3786,2,0))

What this does is to find a match for cell B3 on the FLCR2 tab within the
range; if the match is not found "Not Found" is returned; if the match is
found Excel looks up the value in the cell to the right of the match and
returns it to the cell containing the formula.

I need to add an additional parameter. Sometimes, the value in B3 matches
several values in column A on FLCR2 tab. Right now, the formula returns the
cell to the right of the first row in which the match occurrs. I would like
it to return the HIGHEST of the values in the cells to the right of any row
it matches. Note, you can't narrow down in advance the range of the rows it
will match=it must be dynamic in the function.

Is this possible, and what additional parameters must I add into the above
function?

Thanks for your help.

--

Dave Peterson


--

Dave Peterson

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The if portion of that statement actually returns an array of the values in
B3:B3786 when column A matches the value in B3 or Falses when they don't match.

Maybe if it was written this way, it would be easier to see:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

The =max() picks out the largest value and ignores the falses.

My next guess is that you have an error in B3:B3786--either a formula that
returns an error or the text (after paste special|values).

Remember to look in any hidden rows (manual or autofilter).

Tenacity wrote:

I meant what you said, ie, put that formula in another cell, but use B3 as
the value to match on. Sorry. For this discussion, assume the formula is in
C3 on Sheet 1.

Your new formula returns #VALUE! in all the cells into which it is copied,
so unfortunately it's not working.

One problem I see is that your formula seems to say that if B3 equals any
text in FLCR2!$A$3:$A$3786, then it will return the highest numerical value
in all 3783 rows of B on the FLCR tab, ie the highest value found in all the
cells between B3:B3786.

I want the following: if B3 matches, say A14, A18, A23 and A345 on FLCR2,
then I want the formula to return into C3 (the place where is the formula)
the HIGHEST value of B14, B18, B23 and B345 on FLCR2. That is why the MATCH
and VLOOKUP from my formula seem necessary to me, but I want to add to my
formula the concept of MAX or something like it in order to return the
HIGHEST value of these 5 cells and only these 5 cells which matched. And the
next time the formula is used in C4, C5 and so on it might involve the
HIGHEST value of 10 or 15 or 3 cells, ie, it always varies.

Hopefully, I have made it clearer and together we can get closer to the
target. Thanks for your help.

"Dave Peterson" wrote:

Nope.

I mean to put that formula in another cell, but use B3 as the value to match on.

If B3 is Text (numbers treated as text ('123, not ASDF)), you could change the
formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The --B3 will coerce B3 from text to numbers.

This array formula (ctrl-shift-enter, right???)

looks at A3:A3786 of flcr2 and compares it to whats in B3 of the sheet with the
formula (or --b3 to convert it to real numbers).

If there's a match, it uses the numbers in B3:B3786 of fldr2. If there isn't a
match, it ignores that number (it actually retures FALSE for that mismatch).

Then =MAX() takes the largest number.



Tenacity wrote:

I guess you mean to substitute
=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))
for the formula now in B3, which I put in my original post.

This does not work, just returns - meaning, I guess, not found.

One point I did not indicate befo col. B is numeric on FLCR1, but is
text on Sheet 1. Perhaps this is why it's not matching properly.

My formula correctly finds the match, but it places in B3 the value in the
first row it finds rather than the highest value in all the rows it finds.

Perhaps using your MAX array in conjunction with my formula? I just don't
know how to integrate both. Or another possiblity you might suggest.

One other thing. Sorry for the elementary question, but pls. give a brief
explanation of what an array function does in this context.

Thanks for your help.

"Dave Peterson" wrote:

So column B is numeric.

One way:

=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(flcr2!$A$3:$A$3786,b3)=0,"No matches",
MAX(IF(flcr2!$A$3:$A$3786=b3,flcr2!$b$3:$b$3786))

(one cell, and still an array formula)

Tenacity wrote:

I have the following formula in my worksheet:

=IF(ISNA(MATCH(B3,FLCR2!$A$3:$A$3786,0)),"Not
Found",VLOOKUP(B3,FLCR2!$A$3:$B$3786,2,0))

What this does is to find a match for cell B3 on the FLCR2 tab within the
range; if the match is not found "Not Found" is returned; if the match is
found Excel looks up the value in the cell to the right of the match and
returns it to the cell containing the formula.

I need to add an additional parameter. Sometimes, the value in B3 matches
several values in column A on FLCR2 tab. Right now, the formula returns the
cell to the right of the first row in which the match occurrs. I would like
it to return the HIGHEST of the values in the cells to the right of any row
it matches. Note, you can't narrow down in advance the range of the rows it
will match=it must be dynamic in the function.

Is this possible, and what additional parameters must I add into the above
function?

Thanks for your help.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi

First, I want to thank you for working this through with you.

When I use my formula (see my original post) in a particular cell, say C3 on
sheet 1, it returns a correct value from FLCR column B, albeit the value from
the first row it matches, not the highest of the rows it matches.

This tells me there is no error in error in B3:B378. I was pretty sure to
begin with since I scrubbed it to make sure that the data was "pure".

If I copy your formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

into C3 and hit ctrl-shif-enter to enter it is an array formula as you
suggested (the correct brackets appear after hitting ctrl-shift-enter), it
returns #VALUE! Same with all cells I try with your formula.

This tells me that, with all due respect, your formula is not working for
this purpose.

Since my formula works for 90% of what is intended, can you suggest a way to
incorporate the MAX concept into my formula so both will work in harmony?

If not, then please suggest another alternative, since this is not working.

Thanks for your help.
"Dave Peterson" wrote:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The if portion of that statement actually returns an array of the values in
B3:B3786 when column A matches the value in B3 or Falses when they don't match.

Maybe if it was written this way, it would be easier to see:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

The =max() picks out the largest value and ignores the falses.

My next guess is that you have an error in B3:B3786--either a formula that
returns an error or the text (after paste special|values).

Remember to look in any hidden rows (manual or autofilter).

Tenacity wrote:

I meant what you said, ie, put that formula in another cell, but use B3 as
the value to match on. Sorry. For this discussion, assume the formula is in
C3 on Sheet 1.

Your new formula returns #VALUE! in all the cells into which it is copied,
so unfortunately it's not working.

One problem I see is that your formula seems to say that if B3 equals any
text in FLCR2!$A$3:$A$3786, then it will return the highest numerical value
in all 3783 rows of B on the FLCR tab, ie the highest value found in all the
cells between B3:B3786.

I want the following: if B3 matches, say A14, A18, A23 and A345 on FLCR2,
then I want the formula to return into C3 (the place where is the formula)
the HIGHEST value of B14, B18, B23 and B345 on FLCR2. That is why the MATCH
and VLOOKUP from my formula seem necessary to me, but I want to add to my
formula the concept of MAX or something like it in order to return the
HIGHEST value of these 5 cells and only these 5 cells which matched. And the
next time the formula is used in C4, C5 and so on it might involve the
HIGHEST value of 10 or 15 or 3 cells, ie, it always varies.

Hopefully, I have made it clearer and together we can get closer to the
target. Thanks for your help.

"Dave Peterson" wrote:

Nope.

I mean to put that formula in another cell, but use B3 as the value to match on.

If B3 is Text (numbers treated as text ('123, not ASDF)), you could change the
formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The --B3 will coerce B3 from text to numbers.

This array formula (ctrl-shift-enter, right???)

looks at A3:A3786 of flcr2 and compares it to whats in B3 of the sheet with the
formula (or --b3 to convert it to real numbers).

If there's a match, it uses the numbers in B3:B3786 of fldr2. If there isn't a
match, it ignores that number (it actually retures FALSE for that mismatch).

Then =MAX() takes the largest number.



Tenacity wrote:

I guess you mean to substitute
=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))
for the formula now in B3, which I put in my original post.

This does not work, just returns - meaning, I guess, not found.

One point I did not indicate befo col. B is numeric on FLCR1, but is
text on Sheet 1. Perhaps this is why it's not matching properly.

My formula correctly finds the match, but it places in B3 the value in the
first row it finds rather than the highest value in all the rows it finds.

Perhaps using your MAX array in conjunction with my formula? I just don't
know how to integrate both. Or another possiblity you might suggest.

One other thing. Sorry for the elementary question, but pls. give a brief
explanation of what an array function does in this context.

Thanks for your help.

"Dave Peterson" wrote:

So column B is numeric.

One way:

=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(flcr2!$A$3:$A$3786,b3)=0,"No matches",
MAX(IF(flcr2!$A$3:$A$3786=b3,flcr2!$b$3:$b$3786))

(one cell, and still an array formula)

Tenacity wrote:

I have the following formula in my worksheet:

=IF(ISNA(MATCH(B3,FLCR2!$A$3:$A$3786,0)),"Not
Found",VLOOKUP(B3,FLCR2!$A$3:$B$3786,2,0))

What this does is to find a match for cell B3 on the FLCR2 tab within the
range; if the match is not found "Not Found" is returned; if the match is
found Excel looks up the value in the cell to the right of the match and
returns it to the cell containing the formula.

I need to add an additional parameter. Sometimes, the value in B3 matches
several values in column A on FLCR2 tab. Right now, the formula returns the
cell to the right of the first row in which the match occurrs. I would like
it to return the HIGHEST of the values in the cells to the right of any row
it matches. Note, you can't narrow down in advance the range of the rows it
will match=it must be dynamic in the function.

Is this possible, and what additional parameters must I add into the above
function?

Thanks for your help.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Nope. Your =vlookup() formula only tells you that the first match doesn't have
an error next to it. It doesn't say anything about what's next to any other of
the matches.

Did you look for errors?

Go to flcr2 and select column B:
edit|goto|special
Click Constants
uncheck Numbers, Text, Logicals, but leave Errors checked.
Click ok

If you get "no cells found", then try it again:
Go to flcr2 and select column B:
edit|goto|special
Click Formulas <-- This changed.
uncheck Numbers, Text, Logicals, but leave Errors checked.
Click ok

Did you find errors with either of these (or both)?


Tenacity wrote:

Hi

First, I want to thank you for working this through with you.

When I use my formula (see my original post) in a particular cell, say C3 on
sheet 1, it returns a correct value from FLCR column B, albeit the value from
the first row it matches, not the highest of the rows it matches.

This tells me there is no error in error in B3:B378. I was pretty sure to
begin with since I scrubbed it to make sure that the data was "pure".

If I copy your formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

into C3 and hit ctrl-shif-enter to enter it is an array formula as you
suggested (the correct brackets appear after hitting ctrl-shift-enter), it
returns #VALUE! Same with all cells I try with your formula.

This tells me that, with all due respect, your formula is not working for
this purpose.

Since my formula works for 90% of what is intended, can you suggest a way to
incorporate the MAX concept into my formula so both will work in harmony?

If not, then please suggest another alternative, since this is not working.

Thanks for your help.
"Dave Peterson" wrote:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The if portion of that statement actually returns an array of the values in
B3:B3786 when column A matches the value in B3 or Falses when they don't match.

Maybe if it was written this way, it would be easier to see:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

The =max() picks out the largest value and ignores the falses.

My next guess is that you have an error in B3:B3786--either a formula that
returns an error or the text (after paste special|values).

Remember to look in any hidden rows (manual or autofilter).

Tenacity wrote:

I meant what you said, ie, put that formula in another cell, but use B3 as
the value to match on. Sorry. For this discussion, assume the formula is in
C3 on Sheet 1.

Your new formula returns #VALUE! in all the cells into which it is copied,
so unfortunately it's not working.

One problem I see is that your formula seems to say that if B3 equals any
text in FLCR2!$A$3:$A$3786, then it will return the highest numerical value
in all 3783 rows of B on the FLCR tab, ie the highest value found in all the
cells between B3:B3786.

I want the following: if B3 matches, say A14, A18, A23 and A345 on FLCR2,
then I want the formula to return into C3 (the place where is the formula)
the HIGHEST value of B14, B18, B23 and B345 on FLCR2. That is why the MATCH
and VLOOKUP from my formula seem necessary to me, but I want to add to my
formula the concept of MAX or something like it in order to return the
HIGHEST value of these 5 cells and only these 5 cells which matched. And the
next time the formula is used in C4, C5 and so on it might involve the
HIGHEST value of 10 or 15 or 3 cells, ie, it always varies.

Hopefully, I have made it clearer and together we can get closer to the
target. Thanks for your help.

"Dave Peterson" wrote:

Nope.

I mean to put that formula in another cell, but use B3 as the value to match on.

If B3 is Text (numbers treated as text ('123, not ASDF)), you could change the
formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The --B3 will coerce B3 from text to numbers.

This array formula (ctrl-shift-enter, right???)

looks at A3:A3786 of flcr2 and compares it to whats in B3 of the sheet with the
formula (or --b3 to convert it to real numbers).

If there's a match, it uses the numbers in B3:B3786 of fldr2. If there isn't a
match, it ignores that number (it actually retures FALSE for that mismatch).

Then =MAX() takes the largest number.



Tenacity wrote:

I guess you mean to substitute
=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))
for the formula now in B3, which I put in my original post.

This does not work, just returns - meaning, I guess, not found.

One point I did not indicate befo col. B is numeric on FLCR1, but is
text on Sheet 1. Perhaps this is why it's not matching properly.

My formula correctly finds the match, but it places in B3 the value in the
first row it finds rather than the highest value in all the rows it finds.

Perhaps using your MAX array in conjunction with my formula? I just don't
know how to integrate both. Or another possiblity you might suggest.

One other thing. Sorry for the elementary question, but pls. give a brief
explanation of what an array function does in this context.

Thanks for your help.

"Dave Peterson" wrote:

So column B is numeric.

One way:

=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(flcr2!$A$3:$A$3786,b3)=0,"No matches",
MAX(IF(flcr2!$A$3:$A$3786=b3,flcr2!$b$3:$b$3786))

(one cell, and still an array formula)

Tenacity wrote:

I have the following formula in my worksheet:

=IF(ISNA(MATCH(B3,FLCR2!$A$3:$A$3786,0)),"Not
Found",VLOOKUP(B3,FLCR2!$A$3:$B$3786,2,0))

What this does is to find a match for cell B3 on the FLCR2 tab within the
range; if the match is not found "Not Found" is returned; if the match is
found Excel looks up the value in the cell to the right of the match and
returns it to the cell containing the formula.

I need to add an additional parameter. Sometimes, the value in B3 matches
several values in column A on FLCR2 tab. Right now, the formula returns the
cell to the right of the first row in which the match occurrs. I would like
it to return the HIGHEST of the values in the cells to the right of any row
it matches. Note, you can't narrow down in advance the range of the rows it
will match=it must be dynamic in the function.

Is this possible, and what additional parameters must I add into the above
function?

Thanks for your help.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

No cells were found during either test.

"Dave Peterson" wrote:

Nope. Your =vlookup() formula only tells you that the first match doesn't have
an error next to it. It doesn't say anything about what's next to any other of
the matches.

Did you look for errors?

Go to flcr2 and select column B:
edit|goto|special
Click Constants
uncheck Numbers, Text, Logicals, but leave Errors checked.
Click ok

If you get "no cells found", then try it again:
Go to flcr2 and select column B:
edit|goto|special
Click Formulas <-- This changed.
uncheck Numbers, Text, Logicals, but leave Errors checked.
Click ok

Did you find errors with either of these (or both)?


Tenacity wrote:

Hi

First, I want to thank you for working this through with you.

When I use my formula (see my original post) in a particular cell, say C3 on
sheet 1, it returns a correct value from FLCR column B, albeit the value from
the first row it matches, not the highest of the rows it matches.

This tells me there is no error in error in B3:B378. I was pretty sure to
begin with since I scrubbed it to make sure that the data was "pure".

If I copy your formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

into C3 and hit ctrl-shif-enter to enter it is an array formula as you
suggested (the correct brackets appear after hitting ctrl-shift-enter), it
returns #VALUE! Same with all cells I try with your formula.

This tells me that, with all due respect, your formula is not working for
this purpose.

Since my formula works for 90% of what is intended, can you suggest a way to
incorporate the MAX concept into my formula so both will work in harmony?

If not, then please suggest another alternative, since this is not working.

Thanks for your help.
"Dave Peterson" wrote:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The if portion of that statement actually returns an array of the values in
B3:B3786 when column A matches the value in B3 or Falses when they don't match.

Maybe if it was written this way, it would be easier to see:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

The =max() picks out the largest value and ignores the falses.

My next guess is that you have an error in B3:B3786--either a formula that
returns an error or the text (after paste special|values).

Remember to look in any hidden rows (manual or autofilter).

Tenacity wrote:

I meant what you said, ie, put that formula in another cell, but use B3 as
the value to match on. Sorry. For this discussion, assume the formula is in
C3 on Sheet 1.

Your new formula returns #VALUE! in all the cells into which it is copied,
so unfortunately it's not working.

One problem I see is that your formula seems to say that if B3 equals any
text in FLCR2!$A$3:$A$3786, then it will return the highest numerical value
in all 3783 rows of B on the FLCR tab, ie the highest value found in all the
cells between B3:B3786.

I want the following: if B3 matches, say A14, A18, A23 and A345 on FLCR2,
then I want the formula to return into C3 (the place where is the formula)
the HIGHEST value of B14, B18, B23 and B345 on FLCR2. That is why the MATCH
and VLOOKUP from my formula seem necessary to me, but I want to add to my
formula the concept of MAX or something like it in order to return the
HIGHEST value of these 5 cells and only these 5 cells which matched. And the
next time the formula is used in C4, C5 and so on it might involve the
HIGHEST value of 10 or 15 or 3 cells, ie, it always varies.

Hopefully, I have made it clearer and together we can get closer to the
target. Thanks for your help.

"Dave Peterson" wrote:

Nope.

I mean to put that formula in another cell, but use B3 as the value to match on.

If B3 is Text (numbers treated as text ('123, not ASDF)), you could change the
formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The --B3 will coerce B3 from text to numbers.

This array formula (ctrl-shift-enter, right???)

looks at A3:A3786 of flcr2 and compares it to whats in B3 of the sheet with the
formula (or --b3 to convert it to real numbers).

If there's a match, it uses the numbers in B3:B3786 of fldr2. If there isn't a
match, it ignores that number (it actually retures FALSE for that mismatch).

Then =MAX() takes the largest number.



Tenacity wrote:

I guess you mean to substitute
=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))
for the formula now in B3, which I put in my original post.

This does not work, just returns - meaning, I guess, not found.

One point I did not indicate befo col. B is numeric on FLCR1, but is
text on Sheet 1. Perhaps this is why it's not matching properly.

My formula correctly finds the match, but it places in B3 the value in the
first row it finds rather than the highest value in all the rows it finds.

Perhaps using your MAX array in conjunction with my formula? I just don't
know how to integrate both. Or another possiblity you might suggest.

One other thing. Sorry for the elementary question, but pls. give a brief
explanation of what an array function does in this context.

Thanks for your help.

"Dave Peterson" wrote:

So column B is numeric.

One way:

=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(flcr2!$A$3:$A$3786,b3)=0,"No matches",
MAX(IF(flcr2!$A$3:$A$3786=b3,flcr2!$b$3:$b$3786))

(one cell, and still an array formula)

Tenacity wrote:

I have the following formula in my worksheet:

=IF(ISNA(MATCH(B3,FLCR2!$A$3:$A$3786,0)),"Not
Found",VLOOKUP(B3,FLCR2!$A$3:$B$3786,2,0))

What this does is to find a match for cell B3 on the FLCR2 tab within the
range; if the match is not found "Not Found" is returned; if the match is
found Excel looks up the value in the cell to the right of the match and
returns it to the cell containing the formula.

I need to add an additional parameter. Sometimes, the value in B3 matches
several values in column A on FLCR2 tab. Right now, the formula returns the
cell to the right of the first row in which the match occurrs. I would like
it to return the HIGHEST of the values in the cells to the right of any row
it matches. Note, you can't narrow down in advance the range of the rows it
will match=it must be dynamic in the function.

Is this possible, and what additional parameters must I add into the above
function?

Thanks for your help.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Sorry, I don't have another guess.

Maybe someone else will jump in.

Tenacity wrote:

No cells were found during either test.

"Dave Peterson" wrote:

Nope. Your =vlookup() formula only tells you that the first match doesn't have
an error next to it. It doesn't say anything about what's next to any other of
the matches.

Did you look for errors?

Go to flcr2 and select column B:
edit|goto|special
Click Constants
uncheck Numbers, Text, Logicals, but leave Errors checked.
Click ok

If you get "no cells found", then try it again:
Go to flcr2 and select column B:
edit|goto|special
Click Formulas <-- This changed.
uncheck Numbers, Text, Logicals, but leave Errors checked.
Click ok

Did you find errors with either of these (or both)?


Tenacity wrote:

Hi

First, I want to thank you for working this through with you.

When I use my formula (see my original post) in a particular cell, say C3 on
sheet 1, it returns a correct value from FLCR column B, albeit the value from
the first row it matches, not the highest of the rows it matches.

This tells me there is no error in error in B3:B378. I was pretty sure to
begin with since I scrubbed it to make sure that the data was "pure".

If I copy your formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

into C3 and hit ctrl-shif-enter to enter it is an array formula as you
suggested (the correct brackets appear after hitting ctrl-shift-enter), it
returns #VALUE! Same with all cells I try with your formula.

This tells me that, with all due respect, your formula is not working for
this purpose.

Since my formula works for 90% of what is intended, can you suggest a way to
incorporate the MAX concept into my formula so both will work in harmony?

If not, then please suggest another alternative, since this is not working.

Thanks for your help.
"Dave Peterson" wrote:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The if portion of that statement actually returns an array of the values in
B3:B3786 when column A matches the value in B3 or Falses when they don't match.

Maybe if it was written this way, it would be easier to see:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

The =max() picks out the largest value and ignores the falses.

My next guess is that you have an error in B3:B3786--either a formula that
returns an error or the text (after paste special|values).

Remember to look in any hidden rows (manual or autofilter).

Tenacity wrote:

I meant what you said, ie, put that formula in another cell, but use B3 as
the value to match on. Sorry. For this discussion, assume the formula is in
C3 on Sheet 1.

Your new formula returns #VALUE! in all the cells into which it is copied,
so unfortunately it's not working.

One problem I see is that your formula seems to say that if B3 equals any
text in FLCR2!$A$3:$A$3786, then it will return the highest numerical value
in all 3783 rows of B on the FLCR tab, ie the highest value found in all the
cells between B3:B3786.

I want the following: if B3 matches, say A14, A18, A23 and A345 on FLCR2,
then I want the formula to return into C3 (the place where is the formula)
the HIGHEST value of B14, B18, B23 and B345 on FLCR2. That is why the MATCH
and VLOOKUP from my formula seem necessary to me, but I want to add to my
formula the concept of MAX or something like it in order to return the
HIGHEST value of these 5 cells and only these 5 cells which matched. And the
next time the formula is used in C4, C5 and so on it might involve the
HIGHEST value of 10 or 15 or 3 cells, ie, it always varies.

Hopefully, I have made it clearer and together we can get closer to the
target. Thanks for your help.

"Dave Peterson" wrote:

Nope.

I mean to put that formula in another cell, but use B3 as the value to match on.

If B3 is Text (numbers treated as text ('123, not ASDF)), you could change the
formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The --B3 will coerce B3 from text to numbers.

This array formula (ctrl-shift-enter, right???)

looks at A3:A3786 of flcr2 and compares it to whats in B3 of the sheet with the
formula (or --b3 to convert it to real numbers).

If there's a match, it uses the numbers in B3:B3786 of fldr2. If there isn't a
match, it ignores that number (it actually retures FALSE for that mismatch).

Then =MAX() takes the largest number.



Tenacity wrote:

I guess you mean to substitute
=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))
for the formula now in B3, which I put in my original post.

This does not work, just returns - meaning, I guess, not found.

One point I did not indicate befo col. B is numeric on FLCR1, but is
text on Sheet 1. Perhaps this is why it's not matching properly.

My formula correctly finds the match, but it places in B3 the value in the
first row it finds rather than the highest value in all the rows it finds.

Perhaps using your MAX array in conjunction with my formula? I just don't
know how to integrate both. Or another possiblity you might suggest.

One other thing. Sorry for the elementary question, but pls. give a brief
explanation of what an array function does in this context.

Thanks for your help.

"Dave Peterson" wrote:

So column B is numeric.

One way:

=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(flcr2!$A$3:$A$3786,b3)=0,"No matches",
MAX(IF(flcr2!$A$3:$A$3786=b3,flcr2!$b$3:$b$3786))

(one cell, and still an array formula)

Tenacity wrote:

I have the following formula in my worksheet:

=IF(ISNA(MATCH(B3,FLCR2!$A$3:$A$3786,0)),"Not
Found",VLOOKUP(B3,FLCR2!$A$3:$B$3786,2,0))

What this does is to find a match for cell B3 on the FLCR2 tab within the
range; if the match is not found "Not Found" is returned; if the match is
found Excel looks up the value in the cell to the right of the match and
returns it to the cell containing the formula.

I need to add an additional parameter. Sometimes, the value in B3 matches
several values in column A on FLCR2 tab. Right now, the formula returns the
cell to the right of the first row in which the match occurrs. I would like
it to return the HIGHEST of the values in the cells to the right of any row
it matches. Note, you can't narrow down in advance the range of the rows it
will match=it must be dynamic in the function.

Is this possible, and what additional parameters must I add into the above
function?

Thanks for your help.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Is there a way of combining my formula, which works, with your MAX concept in
the same formula so it returns the highest of the matched cells? I can't
figure out how to do that. Thanks for your help.

"Dave Peterson" wrote:

Sorry, I don't have another guess.

Maybe someone else will jump in.

Tenacity wrote:

No cells were found during either test.

"Dave Peterson" wrote:

Nope. Your =vlookup() formula only tells you that the first match doesn't have
an error next to it. It doesn't say anything about what's next to any other of
the matches.

Did you look for errors?

Go to flcr2 and select column B:
edit|goto|special
Click Constants
uncheck Numbers, Text, Logicals, but leave Errors checked.
Click ok

If you get "no cells found", then try it again:
Go to flcr2 and select column B:
edit|goto|special
Click Formulas <-- This changed.
uncheck Numbers, Text, Logicals, but leave Errors checked.
Click ok

Did you find errors with either of these (or both)?


Tenacity wrote:

Hi

First, I want to thank you for working this through with you.

When I use my formula (see my original post) in a particular cell, say C3 on
sheet 1, it returns a correct value from FLCR column B, albeit the value from
the first row it matches, not the highest of the rows it matches.

This tells me there is no error in error in B3:B378. I was pretty sure to
begin with since I scrubbed it to make sure that the data was "pure".

If I copy your formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

into C3 and hit ctrl-shif-enter to enter it is an array formula as you
suggested (the correct brackets appear after hitting ctrl-shift-enter), it
returns #VALUE! Same with all cells I try with your formula.

This tells me that, with all due respect, your formula is not working for
this purpose.

Since my formula works for 90% of what is intended, can you suggest a way to
incorporate the MAX concept into my formula so both will work in harmony?

If not, then please suggest another alternative, since this is not working.

Thanks for your help.
"Dave Peterson" wrote:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The if portion of that statement actually returns an array of the values in
B3:B3786 when column A matches the value in B3 or Falses when they don't match.

Maybe if it was written this way, it would be easier to see:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786,false))

The =max() picks out the largest value and ignores the falses.

My next guess is that you have an error in B3:B3786--either a formula that
returns an error or the text (after paste special|values).

Remember to look in any hidden rows (manual or autofilter).

Tenacity wrote:

I meant what you said, ie, put that formula in another cell, but use B3 as
the value to match on. Sorry. For this discussion, assume the formula is in
C3 on Sheet 1.

Your new formula returns #VALUE! in all the cells into which it is copied,
so unfortunately it's not working.

One problem I see is that your formula seems to say that if B3 equals any
text in FLCR2!$A$3:$A$3786, then it will return the highest numerical value
in all 3783 rows of B on the FLCR tab, ie the highest value found in all the
cells between B3:B3786.

I want the following: if B3 matches, say A14, A18, A23 and A345 on FLCR2,
then I want the formula to return into C3 (the place where is the formula)
the HIGHEST value of B14, B18, B23 and B345 on FLCR2. That is why the MATCH
and VLOOKUP from my formula seem necessary to me, but I want to add to my
formula the concept of MAX or something like it in order to return the
HIGHEST value of these 5 cells and only these 5 cells which matched. And the
next time the formula is used in C4, C5 and so on it might involve the
HIGHEST value of 10 or 15 or 3 cells, ie, it always varies.

Hopefully, I have made it clearer and together we can get closer to the
target. Thanks for your help.

"Dave Peterson" wrote:

Nope.

I mean to put that formula in another cell, but use B3 as the value to match on.

If B3 is Text (numbers treated as text ('123, not ASDF)), you could change the
formula:

=MAX(IF(FLCR2!$A$3:$A$3786=--b3,FLCR2!$B$3:$B$3786))

The --B3 will coerce B3 from text to numbers.

This array formula (ctrl-shift-enter, right???)

looks at A3:A3786 of flcr2 and compares it to whats in B3 of the sheet with the
formula (or --b3 to convert it to real numbers).

If there's a match, it uses the numbers in B3:B3786 of fldr2. If there isn't a
match, it ignores that number (it actually retures FALSE for that mismatch).

Then =MAX() takes the largest number.



Tenacity wrote:

I guess you mean to substitute
=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))
for the formula now in B3, which I put in my original post.

This does not work, just returns - meaning, I guess, not found.

One point I did not indicate befo col. B is numeric on FLCR1, but is
text on Sheet 1. Perhaps this is why it's not matching properly.

My formula correctly finds the match, but it places in B3 the value in the
first row it finds rather than the highest value in all the rows it finds.

Perhaps using your MAX array in conjunction with my formula? I just don't
know how to integrate both. Or another possiblity you might suggest.

One other thing. Sorry for the elementary question, but pls. give a brief
explanation of what an array function does in this context.

Thanks for your help.

"Dave Peterson" wrote:

So column B is numeric.

One way:

=MAX(IF(FLCR2!$A$3:$A$3786=b3,FLCR2!$B$3:$B$3786))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(flcr2!$A$3:$A$3786,b3)=0,"No matches",
MAX(IF(flcr2!$A$3:$A$3786=b3,flcr2!$b$3:$b$3786))

(one cell, and still an array formula)

Tenacity wrote:

I have the following formula in my worksheet:

=IF(ISNA(MATCH(B3,FLCR2!$A$3:$A$3786,0)),"Not
Found",VLOOKUP(B3,FLCR2!$A$3:$B$3786,2,0))

What this does is to find a match for cell B3 on the FLCR2 tab within the
range; if the match is not found "Not Found" is returned; if the match is
found Excel looks up the value in the cell to the right of the match and
returns it to the cell containing the formula.

I need to add an additional parameter. Sometimes, the value in B3 matches
several values in column A on FLCR2 tab. Right now, the formula returns the
cell to the right of the first row in which the match occurrs. I would like
it to return the HIGHEST of the values in the cells to the right of any row
it matches. Note, you can't narrow down in advance the range of the rows it
will match=it must be dynamic in the function.

Is this possible, and what additional parameters must I add into the above
function?

Thanks for your help.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Maybe you could insert another column next to column B in Fclr2.

=if(a3=sheet1!b3,b3,false)
or
=if(a3=sheet1!b3,--b3,false)

drag down the column and do
=max(FLCR2!$c$3:$c$3786)

Heck, that might be worth doing just to see if you find a problem.

=====
And just to double check, you are hitting ctrl-shift-enter with those formulas,
right?



Tenacity wrote:

Is there a way of combining my formula, which works, with your MAX concept in
the same formula so it returns the highest of the matched cells? I can't
figure out how to do that. Thanks for your help.

"Dave Peterson" wrote:

Sorry, I don't have another guess.
<<snipped