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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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 )?
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,440
Messages
6,136,632
Members
450,022
Latest member
Joel1122331

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
Back
Top