If MATCH function where number not found

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
109
If I have a sequence of numbers running vertically in column A, e.g

1901
1902
1903...up to 1952

If I have a number in cell G3 that is not found in my list, eg 1953, I want the function to then give me the number provided in G4 instead

I know i'll want an IF function but cant figure it out. Hopefully that makes sense

Any ideas please anyone?
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
What do you want to return if G3 is found in the list?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
Ok, how about
=IF(ISNA(MATCH(G3,A2:A20,0)),G4,H3+1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Another way using COUNTIF:

=IF(COUNTIF(A1:A52,G3),H3+1,G4)
 

Forum statistics

Threads
1,089,223
Messages
5,406,948
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top