Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-23-2021, 01:26 PM
tomohawk tomohawk is offline Help with Excel Formula Windows 10 Help with Excel Formula Office 2019
Novice
Help with Excel Formula
 
Join Date: Sep 2020
Posts: 16
tomohawk is on a distinguished road
Default Help with Excel Formula

Hi,

I have been referred to this forum from techsupportforum, so hopefully somebody will be able to help me.

In the attached Excel file I have 2 sheets.



In sheet "CPU Database", I want to add a formula in cell J2 as follows:-
IF the value in A2 is replicated in sheet "W11 Supported (Intel Core)"/Range A2:A256
Then return a value = "Yes"
If NOT, return a value of "No"

Tommy
Attached Files
File Type: xlsx Intel W11 CPU Data.xlsx (15.4 KB, 4 views)
Reply With Quote
  #2  
Old 11-24-2021, 12:00 AM
ArviLaanemets ArviLaanemets is offline Help with Excel Formula Windows 8 Help with Excel Formula Office 2016
Expert
 
Join Date: May 2017
Posts: 656
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

Code:
=IFERROR(IF(VLOOKUP($A2,'W11 Supported (Intel Core)'!$A$2:$A$256,1,0)>"","Yes","No"),"No")
IF part will really never return FALSE, returning an error instead - so you have to use IFERROR to cover this.
Reply With Quote
  #3  
Old 11-24-2021, 12:58 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Help with Excel Formula Windows 7 64bit Help with Excel Formula Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,524
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Arvi, I think there is a flaw in your answer.
The text returned by the VLOOKUP will ALWAYS be larger than the null text string. The formula seems to return "yes" always
Perhaps
Code:
=IF(countif('CPU Database'!$A$2:$A$44,$a2),"yes","no")
Reply With Quote
  #4  
Old 11-24-2021, 01:36 AM
ArviLaanemets ArviLaanemets is offline Help with Excel Formula Windows 8 Help with Excel Formula Office 2016
Expert
 
Join Date: May 2017
Posts: 656
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

Quote:
Originally Posted by Pecoflyer View Post
The text returned by the VLOOKUP will ALWAYS be larger than the null text string. The formula seems to return "yes" always
It works for me!

No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
Yes i3-9300 Yes
Yes i3-9320 Yes
Yes i3-9350K Yes
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A

1st column is returned by actual formula;
2nd column is returned by VLookup();
3rd column is returned by If().

With 4th parameter 0/False, VLOOKUP returns #N/A always, when no exact match was found!
Reply With Quote
  #5  
Old 11-24-2021, 01:55 AM
tomohawk tomohawk is offline Help with Excel Formula Windows 10 Help with Excel Formula Office 2019
Novice
Help with Excel Formula
 
Join Date: Sep 2020
Posts: 16
tomohawk is on a distinguished road
Default

Thanks for the support ArviLaanemets.

That worked perfectly
Reply With Quote
  #6  
Old 11-24-2021, 07:08 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Help with Excel Formula Windows 7 64bit Help with Excel Formula Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,524
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Quote:
Originally Posted by ArviLaanemets View Post
It works for me!

No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
Yes i3-9300 Yes
Yes i3-9320 Yes
Yes i3-9350K Yes
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A
No #N/A #N/A

1st column is returned by actual formula;
2nd column is returned by VLookup();
3rd column is returned by If().

With 4th parameter 0/False, VLOOKUP returns #N/A always, when no exact match was found!
Strangely for me it does not
Attached Files
File Type: xlsx Copy of Intel W11 CPU Data.xlsx (18.7 KB, 1 views)
Reply With Quote
  #7  
Old 11-24-2021, 07:16 AM
ArviLaanemets ArviLaanemets is offline Help with Excel Formula Windows 8 Help with Excel Formula Office 2016
Expert
 
Join Date: May 2017
Posts: 656
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

Quote:
Originally Posted by Pecoflyer View Post
Strangely for me it does not
Because the formula is on wrong sheet! You are checking, do Model's displayed on sheet 'W11 Supported (Intel Core)' exist on sheet 'W11 Supported (Intel Core)'!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Formula - Help guiverdi Excel 2 07-23-2017 11:48 PM
Help with Excel Formula excel if formula LPerry Excel 2 03-06-2017 07:14 AM
excel formula jcaal50 Excel 6 05-16-2014 04:01 PM
excel help Formula sellspeed Excel 15 03-10-2010 10:57 AM
Help with Excel Formula Excel Formula Help Shahzad Excel 1 12-07-2008 04:13 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft