If MATCH function where number not found

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
86
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
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
30,247
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
30,247
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,077,850
Messages
5,336,740
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top