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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

GlennUK

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

ADVERTISEMENT

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,136,597
Messages
5,676,719
Members
419,647
Latest member
usas12gthr

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