• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Join IF,MID & V lookup function

Sreehari

Member
please help me with a formula which work like nested if,

requirement

I have a digit like "18964" - if look up matches the value needs to be updated in column if not match then using MID look up for "1896"(cutting last digit)if not matches using MID look up for "189" if not matches using MID look up for "18"
 
Hi Sreehari ,

Even though you will upload a file , please specify the limits :

18964 is not a digit , but a number with 5 digits ; what is the maximum number of digits possible ?

You have mentioned the following lookups : 18964 , 1896 , 189 and 18 ; what about 1 ? How many lookups will need to be done ?

Narayan
 
@shrihari, if you are looking like this. then try this formula.

=IF(LEN(A1)=5,VLOOKUP(A1,A:B,2,0),IF(LEN(A1)<>5,VLOOKUP(--MID(A1,1,4),A:B,2,0),IF(LEN(A1)<>4,VLOOKUP(--MID(A1,1,3),A:B,2,0),IF(LEN(A1)<>3,VLOOKUP(--MID(A1,1,2),A:B,2,0)))))
 
@Ramesh Deo

Firstly, no need to be sorry.

Actually, it's more of a drill down requirement. Search for a number if not found go for searching a less digit from right most side and so on.

Now, the problem will be something as raised by @NARAYANK991 Sir, how long will be the number will be, and how deep we need to go down?

Will we have to stop when we are left with only 2 digit (in case we did not find numbers higher than it) or we may can go for 1 digit also?

I think OP has to clarify his requirement in more details.

Regards,
 
file up load is not working from my office system.

pasted one sample below. try copy pase this in XL at your end.

source contain 3 column & working contain 3 column

Source
Code first Second Code first Second
42862 XYZ ABC 428629 V lookup work for digit 42862 and place value XYZ V lookup work for digit 42862 and place value ABC
42863 ABC XYZ 4286298 V lookup work for digit 42862 and place value ABC V lookup work for digit 42862 and place value XYZ
4287 XYZ ABC 42863978 V lookup work for digit 42863and place value XYZ V lookup work for digit 42863 and place value ABC
42867 EFG HIJ 428679378 V lookup work for digit 42867and place value EFG V lookup work for digit 42863 and place value HIJ
 
Misra is correct ..

this is a drill down requirement which Search for a number in column A if not found go for searching a less 1 digit from right most side and so on till it left with 2 digit,

i have a file with more than 3000 rows contains numbers of different length randomly in column A which i need to lookup with drill down .

I tried with the above formula shared but not working and also since the number length vary randomly in rows kinldy suggest a modified fromula.
 
@Sreehari

See the attached file. I had used UDF to get the result as the number of digits can vary and it will lead to long formula. Just try it and if you face difficulty in making the code as per your sheet, write back.

Regards,
 

Attachments

  • UDFDrill.xlsm
    16.8 KB · Views: 9
Last edited:
For posterity, following formula committed by CTRL+SHIFT+ENTER should also give similar results.
In cell E1 based on file posted by Somendra:
=LOOKUP(2,(FIND(MAX(ISNUMBER(MATCH(MID(D1,1,LEN(D1)-(ROW($A$1:INDEX(A:A,LEN(D1)-1))-1))&"*",$A$1:$A$4&"",0))*MID(D1,1,LEN(D1)-(ROW($A$1:INDEX(A:A,LEN(D1)-1))-1))),$A$1:$A$4,1)=1)+0,$B$1:$B$4)
Copy down

F1:
=LOOKUP(2,(FIND(MAX(ISNUMBER(MATCH(MID(D1,1,LEN(D1)-(ROW($A$1:INDEX(A:A,LEN(D1)-1))-1))&"*",$A$1:$A$4&"",0))*MID(D1,1,LEN(D1)-(ROW($A$1:INDEX(A:A,LEN(D1)-1))-1))),$A$1:$A$4,1)=1)+0,$C$1:$C$4)
Copy down

Just keep in mind Array formulas will be slow with big workbooks as Somendra has suggested.
 
Just thought if your number always start with position as 1, than try below array formula in my sample file :

=INDEX(B$1:B$5,MATCH(TRUE,ISNUMBER(--(SEARCH($A$1:$A$5&"*",$D1)=1)),0))

In E1 and copy right and down.

Confirm with Ctrl+Shift+Enter.

Regards,
 
@somendra

UDF file shared is working fine but taking lots of time( can i get the source code/formula used in this UDF)

above formula [=INDEX(B$1:B$5,MATCH(TRUE,ISNUMBER(--(SEARCH($A$1:$A$5&"*",$D1)=1)),0))]is also working ,but while copy paste the formula to rest of the cells is giving an error message. "you cannot change part of an array" please suggest a solution to tackle this .other wise the formula may work faster than UDF i think..

@srivallabha,

i tried formula shared by you , but the giving wrong result hope some modification in formula is required, also the above issue of copy paste the formula to rest of the cells shows the above mentioned error.
i need to copy and past the formula one by one in each cells to get the out put. my working files normaly may containg more than 50000 rows hence the dificulty.
 
Thanks all my awesome EXCEL ninjas for the earnest support and enthu shown in supporting me ..this will definitely going to help in my analysis..

a special thanks to somendra ninja..

now it’s working fine and is faster than UDF..

see you all again with my new similar doubts ..:):awesome:
 
Hi Sreehari,

Thanks for the feedback.

Check this file, it uses different UDF, check its performance. Yellow cells is data. Green Cells are lookup value, orange cells is old udf and blue cells are new udf.

Regards,
 

Attachments

  • UDFDrill.xlsm
    19.3 KB · Views: 12
superb , it is faster than old UDF and formula used.

Thanks a lot ..

Also can i get the source code also ,so that i can use is for my other similar analysis.

you may either attach it here or send to my email:kavissnh@gmail.com
 
@Sreehari

Thanks for the feedback. The code is in the file. Press Alt+F11 to access VBA Editor. On the Left Hand side of VBE (Visual Basic Editor) you can see Project Explorer if not visible Press Ctrl+R. There you can see modules under your file name, just double click the same to see the code of both the UDF's. Any way below is attached both the codes.

Code:
Function drillVlookUp(rng As Range, col As Long) As Variant

Dim val As Variant
Dim lr As Long


val = rng.Value
err = 0

lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = Len(rng) To 2 Step -1
For j = 1 To lr
    If Range("A" & j) = val Then
        drillVlookUp = Cells(j, col)
        err = 1
    Exit Function
    End If
Next j
val = Left(val, i - 1) + 0
Next i

If err = 0 Then
drillVlookUp = "Value Not Found"
End If
End Function

Code:
Function vlookup2(rng As Range, col As Long) As Variant

Dim val As Variant
Dim lr As Long
Dim sourcedata As Range
Dim ws As Worksheet
Dim result As Variant
Dim err As Integer

Set ws = ThisWorkbook.Worksheets("Sheet1")
val = rng.Value
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

Set sourcedata = ws.Range("A1:C" & lr)

err = 0

Do While err <= 0

result = Application.VLookup(val, sourcedata, col, 0)

If IsError(result) Then

    val = Left(val, Len(val) - 1) + 0
    If Len(val) < 2 Then
    err = 0
    GoTo a:
    End If
    Else
vlookup2 = result
err = 1
End If
Loop
a:
If err = 0 Then
vlookup2 = "Value Not in List"

End If

End Function

Regards,
 
Last edited:
That's great ..thanks a lot..

There is one more area I am facing issue for which I need your support..

Attached one sample file

The requirement is the values in field "file name" have a name and date which I am currently extracting with MID function.

But the difficulty is there are n number of rows with each file name and the count of similar file name keep on increasing.so I need to manually apply MID after each type of file name ends which is time consuming.

I am managing with a macro which use Mid and lookup now but I have to handle more than 8 Lac row
 
below are the samples. please copy pase in XL.
MID function used in columns file type and Date


File name File type(MID) Date(MID)
MSCGSK09B2012060119536014 MSCGSK09B 20120601
KER_USTK_20120531000427_0001 KER_USTK 20120531
KL_EAPP_20120531180000_0016 KL_EAPP 20120531
KR_PS_20120627100000_0003 KR_PS 20120627
KR_PS_RT_20120602090000_0004 KR_PS_RT 20120602
KR_RBT_20120606210000_0021 KR_RBT 20120606
KR_TF_01_01668620120618 KR_TF_01 20120618
KRGPRSPSIN120120601060728_4438 KRGPRSPSIN1 20120601
KRGPRSPSRM120120609212918_6744 KRGPRSPSRM1 20120609
 
@Sreehari

Sorry I forgot to post here :)

First there is no standard pattern from which the formula can be triggered. I did formulate to extract file name and date, but here in the sample all dates are of 2012, so that became the triggering point.

Can you confirm can this be taken as standard point.

Regards,
 
the one i shared are some sample names of 2012 but current woking file contains 2014 only .

sorry for this confusion. :)

one more point is in case of file name with MSCGSK09B XXXX the last two alfa numeric chr. may change from 1A to 1D till 15A t 15D ,

Also there are more file name in similar pattern and above mentioned are only some of them.

currently i an using the formula "MID(A2,VLOOKUP(A2,chk!$A$3:$D$91,3,1),VLOOKUP(A2,chk!$A$3:$D$91,4))" for V lookup i am using one table where i have the file name staring chr's with mid length like
Cat Left Mid start Mid characters
KER_BGM 7 8 8
KER_USTK 9 10 8

which run on a macro. but it is taking huge time and increasing the file size.
 
Back
Top