Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Help with Excel Formula Help with Excel Formula
Register FAQ Mark Forums Read

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
Advanced Beginner
Help with Excel Formula 		 
Join Date: Sep 2020
Location: Dublin
Posts: 48
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, 8 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: 873
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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,767
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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: 873
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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
Advanced Beginner
Help with Excel Formula 		 
Join Date: Sep 2020
Location: Dublin
Posts: 48
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,767
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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, 5 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: 873
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode


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 07:49 AM.

Contact Us - Privacy Statement - Top

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