Can you improve this formula?

[Jase L]

=IF('Combined Data'!K2>=$E$2,IF(ISNA(VLOOKUP('Combined Data'!D2,'Combined Data'!$D$2:$D$100,1,FALSE)),#N/A,VLOOKUP('Combined Data'!D2,'Combined Data'!$D$2:$D$100,1,FALSE)),#N/A)

Better yet, does it look correct.

I guess the only thing that matters, is that it works.

 

[GlennUK]

Please explain in English what the purpose of the formula is? For example, why are you doing a lookup of a value that is actually inside the same lookup table? Why are you returning #N/A in an IF when you are ( at the same time ) testing using ISNA instead of letting the VLOOKUP fail ( which it won't using the current logic )?

 

[NBVC]

Here is an alternative:

=IF(OR('Combined Data'!K2>=$E$2,ISNA(MATCH('Combined Data'!D2,'Combined Data'!$D$2:$D$100,0))),"N/A",MATCH('Combined Data'!D2,'Combined Data'!$D$2:$D$100,0))


This will return the vertical position of a match, if one is found (since you're looking with the same column, I assume you'd want to know where it is). If you really want the same data returned, then replace the last match() argument back to your vlookup.

 

[Jase L]

I need to explain that this is something I have done my way, and not necessarily the best way. It works, so I'm happy, but I'm here to learn.

Moving along.

The purpose of this formula is.... how do I explain this.

I have two lots of data for users which needs to be merged - statistical and quality. To merge this data I have created a sheet that looks at both lots of data, and anywhere the User name is the same, it tacks the quality data on the end of the statistical data.

However, to make it more interesting, I need to exclude any data that does not meet certain quality parameters.

So the

=IF('Combined Data'!K2>=$E$2

determines if the data is acceptable. If it is, I need that data to graph, and need #N/A to appear where data is removed. The ISNA part.

It makes sense in my mind!

 

[Dufus]

Jase L,

From the looks of your formula:
If K2 is greater than or equal to E2,
Look at the value of D2 in the list D2:D100,
If D2 is #N/A (wich can only be so if it is #N/A since the lookup list contains D2 by definition),
Return #N/A (which is also the value of D2)
Else return D2
If K2 isn't greater than or equal to E2
Return #N/A

If this is correct, try this:
=IF(K2>=E2,D2,#N/A)

Dufus

 

[Jase L]

Jase L,

From the looks of your formula:
If K2 is greater than or equal to E2,
Look at the value of D2 in the list D2:D100,
If D2 is #N/A (wich can only be so if it is #N/A since the lookup list contains D2 by definition),
Return #N/A (which is also the value of D2)
Else return D2
If K2 isn't greater than or equal to E2
Return #N/A

If this is correct, try this:
=IF(K2>=E2,D2,#N/A)

Dufus

That works.

But, only for one column at a time.

 

[Dufus]

I guess I missed the second column thing. Can you clarify?