Reformat IF(ISERROR(....) : if 1st option returns empty, look at 2nd option.

[sonar]

I have this formula:

=IF(ISERROR(INDEX('8DBC'!$A$1:$BG$500,MATCH($A10,'8DBC'!$A$1:$A$500,0),49)),"",INDEX('8DBC'!$A$1:$BG$500,MATCH($A10,'8DBC'!$A$1:$A$500,0),49))

I need to be able to see if there is data in the range matching up in 8DBC, column 49, if not, to draw information from SI-1, column 4. How do I format this formula to do just that?

I have tried, but I struggle with formula error's

Regards

[Morrigan]

What's your criteria when selecting a proper value from sheet SI-1?

You need a function to select a value from sheet SI-1 when ISERROR() is true.

I have this formula:

=IF(ISERROR(INDEX('8DBC'!$A$1:$BG$500,MATCH($A10,'8DBC'!$A$1:$A$500,0),49)),"",INDEX('8DBC'!$A$1:$BG$500,MATCH($A10,'8DBC'!$A$1:$A$500,0),49))

I need to be able to see if there is data in the range matching up in 8DBC, column 49, if not, to draw information from SI-1, column 4. How do I format this formula to do just that?

I have tried, but I struggle with formula error's

Regards

[sonar]

Hi,

Not to worry, I managed to circumvent the problem by placing the query somewhere else, and let the sheet do a normal extraction.

Thanks

Regards
Sonar

[Biff]

Hi!

In your formula your are testing for an error in a range and if there is one
to return blank. If there is no error, then return the data from that same
range.

However, your description is saying if there is an error in one range then
return data from a completely different range, SI-1, column 4.

You don't need to include the INDEX call inside of ISERROR. You can shorten
your current formula to:

=IF(ISNA(MATCH($A10,'8DBC'!$A$1:$A$500,0)),"",INDEX('8DBC'!$A$1:$BG$500,MATCH($A10,'8DBC'!$A$1:$A$500,0),49))

Biff

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> I have this formula:
>
> =IF(ISERROR(INDEX('8DBC'!$A$1:$BG$500,MATCH($A10,'8DBC'!$A$1:$A$500,0),49)),"",INDEX('8DBC'!$A$1:$BG$500,MATCH($A10,'8DBC'!$A$1:$A$500,0),49))
>
> I need to be able to see if there is data in the range matching up in
> 8DBC, column 49, if not, to draw information from SI-1, column 4. How
> do I format this formula to do just that?
>
> I have tried, but I struggle with formula error's
>
> Regards
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=466880
>