How to extract data if a value is present

**monkdelafunk** · 08-28-2008, 11:28 AM

Yeah, tons of them! Is there a way of ignoring these results as otherwise the cells I'm going to be searching will make my formula huge!

**Ron Coderre** · 08-28-2008, 11:51 AM

It's more professional to fix the formulas so they don't return #NA.

But, if you must have the errors, here are two ARRAY FORMULA options.
Note: Commit array formulas with CTRL+SHIFT+ENTER, instead of just ENTER.

Option_1:
=SUMPRODUCT(IF(ISTEXT(P8:BX182),(LEFT(P8:BX182,1)="H")*MID(P8:BX182,3,255)))

Option_2:
=SUMPRODUCT(IF(NOT(ISERROR(P8:BX182)),(LEFT(P8:BX182,1)="H")*MID(P8:BX182,3,255)))

I hope that helps.

**monkdelafunk** · 08-28-2008, 12:19 PM

Hmmm, getting a value error now. Maybe we should look at fixing the n/a error. The formula that is creating the errors (because it is refering to data that is not yet present) is:

=VLOOKUP($E8,'1st'!$B$4:$Q$340,16,FALSE)

Do we use ISERROR to get rid of the errors? I'll try a few things but any help would be appreciated!

**Ron Coderre** · 08-28-2008, 01:04 PM

Try something like this:

=IF(COUNT(MATCH($E8,'1st'!$B$4:$B$340,0)),VLOOKUP($E8,'1st'!$B$4:$Q$340,16,FALSE),"")

Can you work with that?

**monkdelafunk** · 08-28-2008, 01:04 PM

So I've sorted the n/a error in my original calculations by changing it to:

=IF(ISERROR(VLOOKUP($E8,'1st'!$B$4:$Q$340,16,)),"",(VLOOKUP($E8,'1st'!$B$4:$Q$340,16,FALSE)))

I'm still getting the value error. Any suggestions please Ron/anyone?

Thanks, Chris

**monkdelafunk** · 08-28-2008, 01:08 PM

Thanks Ron - we got there at about the same time on the n/a error!

I don't know where to begin with the value error though, as I don't understand your original formula completely.

Have I done something wrong when inputting my own cell range?

**Ron Coderre** · 08-28-2008, 01:14 PM

Technically, the formulas that you and I posted would trap
the #NA (no matching value) error. Now it's time to look in Col_Q of the
referenced range to see if any of those cells resolve to "#VALUE!".

**monkdelafunk** · 08-28-2008, 01:41 PM

Aha! The cells that this formula is being run over will sometimes contain just a letter (N, S, L etc.). If I run it just on cells with numeric or alpha-numeric containing the H it's fine. So how do we get it to ignore cells that contain just a letter?

Thanks for helping me with this Ron!

Oh yeah, they may also contain 2 letters eg. SU. Thanks!