Help needed to make a file

Hello, Everyone,
I am a new member of this group. Finding difficulty in preparing an excel file.
I have values two columns and in third column I need to match each value in Col A with the values of Column B; if the value in column A is available in B then= the value in A or if it does not match then next available value out of the values in B.
A copy of excel sheet is attached. The expected results are also given for easy reference to use the formula.
Please help in this regrd.

SangeetaDNath,

Welcome to Excel Guru!

Sorry I can't figure out how to do this with only a formula but with the help of a User Defined Function (UDF) it is possible.

If you can sort your table by the Given Value then this formula and User Defined Function will do the job.


Formula: C2: =IF(COUNTIF($B$2:$B$6,$A2)>0,$A2,lNextNumber($A2))

Test with your data:


If you can't sort your table the sort will have to be done in the function which would be very inefficient!

Test File: View attachment Match Next RG V1.xlsm

HTH :cheers:

Sir! Thank you very much for your prompt reply. For the first column the values can't be sorted. It shall be random values . However the second column values shall be in ascending order.

retired007geek said:

SangeetaDNath,

Welcome to Excel Guru!

Sorry I can't figure out how to do this with only a formula but with the help of a User Defined Function (UDF) it is possible.

If you can sort your table by the Given Value then this formula and User Defined Function will do the job.

Code:

Option Explicit

Function lNextNumber(lCV As Long) As Long

    Dim lNum As Variant
    
    For Each lNum In ActiveSheet.Range("GivenValue")
    
       If lNum > lCV Then
         lNextNumber = lNum
         Exit For
       End If
       
    Next lNum
    
End Function  'lNextNumber

Formula: C2: =IF(COUNTIF($B$2:$B$6,$A2)>0,$A2,lNextNumber($A2))

Test with your data:
View attachment 7341

If you can't sort your table the sort will have to be done in the function which would be very inefficient!

Test File: View attachment 7342

HTH :cheers:

Click to expand...