Hi David

Then try

=IF(COUNTIF($N$34:$V$34,X34),MAX(($N$34:$V$34=X34) *$N$35:$V$35),"")

--
Regards

Roger Govier


"DavidFarnsworth" wrote in
message ...
Not quite what I need. In the lookup range are multiples of the
lookup value
and it only returns the first encountered. I need to find the
largest.

Thank you.
David

"Roger Govier" wrote:

Hi David

I wasn't paying full attention in my last post.
The MAX function in your original formula is superfluous.
The Hlookup will only return the first instance of X34 within the
range,
hence Max is working on a single value and doesn't achieve anything.

Change the formula to
=IF(COUNTIF($N$34:$V$34,X34),HLOOKUP(X34,$N$34:$V$ 35,2,FALSE),"")


--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi David

Try the non-array entered formula
=IF(COUNTIF($N$34:$V$34,X34),MAX(HLOOKUP(X34,$N$34 :$V$35,2,FALSE)),"")

--
Regards

Roger Govier


"DavidFarnsworth" wrote
in
message ...
It did help indeed.
I have another question if you don't mind. I have a formula that
provides
part of the lookup for the previous formula you so kindly helped
me
with and
I get the error value #VALUE! from some that don't quite meet the
criteria.
Could you explain how to leave a blank cell if the error value
occurs
with
this formula...
{=IF(OR(EXACT(X34,$N$34:$V$34)),MAX(HLOOKUP(X34,$N $34:$V$35,2,FALSE)),"")}
Thanks again

David

"Roger Govier" wrote:

Hi David

You're more than welcome.
Thanks for the feedback to let us know that it was a solution
that
met
your needs.

--
Regards

Roger Govier


"DavidFarnsworth"
wrote
in
message
...
Thank You!!! I'm just glad someone was able to decipher what I
meant!
Thank YOU!
David

"Roger Govier" wrote:

Hi David

If I understand you correctly, then you will have a series of
text
values in A1:A19
You will have a series of Numbers in B1:B19

You have a series of text values in D1:L119 and you wish to
retrieve
the
value in row 7 where (for example) A1 and B1 match the values
in
D1
and
D2

If that is correct, the following array entered formula should
achieve
what you want
{=INDEX($D$1:$L$19,7,MATCH(A1&B1,$D$1:$L$1&$D$2:$L $2,0))}

To produce an array formula, Commit or Edit using Control +
Shift
+
Enter (CSE) not just Enter.
When you use CSE, Excel will insert the curly braces { }
around
the
formula. Do not type them yourself.

--
Regards

Roger Govier


"DavidFarnsworth"
wrote
in
message
...
Hello,
I have a formula that results with 2 rows of data spread
across
9
columns.
The first row contains text. The second a value.
(Text Text Text Text Text...
6 5 8 16 6... )
I have a column of all possible text (19) with a column
beside
it
that
looksup the text in the first row and retrieves the value
below
it
and
MAXs'
the results.
My next column I wish it to Index the 2 rows to retrieve the
same
column 9
and return a different value 5 rows down. I have been
attempting
to
use
index, match and offset IF the rows do not equal the same
column
with
little
success. I want to know s this possible? And if so, how
would
I
go
about it?

Thank you for your help, David