Can you improve this formula?

Jase L

New Member
Joined
Sep 16, 2006
Messages
13
Code:
=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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,722
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

New Member
Joined
Sep 16, 2006
Messages
13
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
Code:
=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! :biggrin:
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
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

New Member
Joined
Sep 16, 2006
Messages
13
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
:biggrin:

That works. :oops:

But, only for one column at a time.
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
I guess I missed the second column thing. Can you clarify?
 

Forum statistics

Threads
1,171,205
Messages
5,874,344
Members
433,046
Latest member
aniruddh

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
Top