Lookup <> N/A

gino59

Active Member
Joined
Jul 26, 2010
Messages
496
Hi all!

I'm hoping someone can help figure out the right formula for this! I have a row (E11:P11) with the results of a MAX formula from a different row. Basically, the E11:P11 row looks like this...

<TABLE style="WIDTH: 756pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1003><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" span=2 width=85><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" span=4 width=81><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 68pt; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=16 width=90>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=86>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=82>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=81>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=85>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=85>$6,196.45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=86>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 63pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=84>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=81>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=81>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=81>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=81>#N/A</TD></TR></TBODY></TABLE>

Now in cell D11, I'm trying for a formula that will look up E11:P11 and return the single MAX value (or the non-#N/A value), in this case, $6196.45.

Any ideas?

Many thanks as always!!
Cheers,
Gino
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi Gino

Try

Code:
{=MAX(IF(NOT(ISNA(E11:P11)),E11:P11))}

It's an array so confirm with Shift, Crtl and Enter to get the braces {}

:)
 
Upvote 0
Hi all!

I'm hoping someone can help figure out the right formula for this! I have a row (E11:P11) with the results of a MAX formula from a different row. Basically, the E11:P11 row looks like this...

<TABLE style="WIDTH: 756pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1003 border=0><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" span=2 width=85><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" span=4 width=81><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: #376091" width=90 height=16>#N/A</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=86>#N/A</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=82>#N/A</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=81>#N/A</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=85>#N/A</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=85>$6,196.45</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=86>#N/A</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=84>#N/A</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=81>#N/A</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=81>#N/A</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=81>#N/A</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091" width=81>#N/A</TD></TR></TBODY></TABLE>

Now in cell D11, I'm trying for a formula that will look up E11:P11 and return the single MAX value (or the non-#N/A value), in this case, $6196.45.

Any ideas?

Many thanks as always!!
Cheers,
Gino
One way...

=SUMIF(E11:P11,"<1E100")
 
Upvote 0
Try

=LOOKUP(9.99999999999999E307,E11:P11)

Provided there is only 1 numerical value.
If there are multiple numerical values, it will return the one furthest to the right.
 
Upvote 0
Wow guys -THANKS! All of these solutions do the trick!! The lookup bignum is fine also because I only need the max so if they're all the same value then that's good.

Thanks again!!
Cheers,
Gino
 
Upvote 0
Wow guys -THANKS! All of these solutions do the trick!! The lookup bignum is fine also because I only need the max so if they're all the same value then that's good.

Thanks again!!
Cheers,
Gino

Please understand that my solution using Bignum will NOT return the MAX value.
It will return the numerical value that is furthest to the right.
Which is not necessarily the largest numerical value.
It only seems to be working in your situation because there is only 1 numerical value...

If you really want the MAX numerical value, among multiple numerical values,
Then you need to use JazzSP8's solution in post #2.
Enterd with CTRL + SHIFT + ENTER

Or simplified version of that is
=MAX(IF(ISNUMBER(E11:P11),E11:P11))
Also CTRL + SHIFT + ENTER
 
Last edited:
Upvote 0
If you want the MAX, you need to use JazzSP8's solution in post #2.
Enterd with CTRL + SHIFT + ENTER
I interpret this to mean that there will be only one number in the range:
return the single MAX value (or the non-#N/A value)
In which case all of our suggestions will do what the OP asked for.
 
Upvote 0
Thanks again, guys! Yeah, I'm going with the MAX array formula - just on the chance that the row ends up with duplicate values.

Cheers!
Gino
 
Upvote 0

Forum statistics

Threads
1,216,484
Messages
6,130,934
Members
449,607
Latest member
babylegs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top