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