• 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.

Vlookup wildcard

Shabbo

Member
Dear sir,

How can I apply wildcard vlookup for attached sheet in bank statement .
 

Attachments

  • wildcard.xlsx
    9.6 KB · Views: 6
Shabbo
Some questions for You:
What would You vlookup?
... there could find same values from 'Bank Statement's E-column and 'Bank Ledger's F-column ... hmm? Lookup
Why 'Bank Statement's Value Dates have ' in front of date?
Have You checked vlookup's syntax?
eg > https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
Have You checked vlookup with "*"?
eg > https://exceljet.net/formula/partial-match-with-vlookup
Dear Sir,
Thanks for your reply,
My formula is not working, please advise.
 

Attachments

  • wildcard not working.xlsx
    10.9 KB · Views: 5
Shabbo
Yes...
Did You noticed that I tried to ask few questions? ( Sentences which ends with ? )
... including two links ... which ... seems You've skipped.
Would You read those links and after You've read and tested those, ask again if needed?
 
Shabbo
Yes...
Did You noticed that I tried to ask few questions? ( Sentences which ends with ? )
... including two links ... which ... seems You've skipped.
Would You read those links and after You've read and tested those, ask again if needed?
Dear Sir,
Value date and transaction dates are same and no need to focus on it.
I have tried to apply lookup what I understand from links provided by you, but still its not solved....I applied in both sheets but not working.
Please advise.
 

Attachments

  • wildcard not working.xlsx
    11 KB · Views: 3
Shabbo
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

What is You 'What you want to look up'?
... You have written the whole column with * ... hmm?
Do You really would like to ... look up ... the whole column ?
 
Shabbo
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

What is You 'What you want to look up'?
... You have written the whole column with * ... hmm?
Do You really would like to ... look up ... the whole column ?
Dear Sir,
Yes sir, I wanted to lookup whole column..
my problem is that only, when I generate data from bank statement, I get data like column C where I can not get only reference number I get full Narration written by bank, but when I get data from bank ledger I get only reference number available from bank statement and I wanted to lookup.
 
Shabbo
Yes ... I would forget term 'VLOOKUP' ...
How do You do it 'manually'? ( = without computer keyboard )
Of course, You could 'see' which 'part' You would try to find from other sheet ...
If You could write a formula to get that 'part' from 'full Narration' then You could use that as 'manually'.
Otherwise ... could You do it opposite way?
You have 'only reference number' ... You could use that to find it from 'full Narration'.
 
Shabbo
Yes ... I would forget term 'VLOOKUP' ...
How do You do it 'manually'? ( = without computer keyboard )
Of course, You could 'see' which 'part' You would try to find from other sheet ...
If You could write a formula to get that 'part' from 'full Narration' then You could use that as 'manually'.
Otherwise ... could You do it opposite way?
You have 'only reference number' ... You could use that to find it from 'full Narration'.
Dear Sir
Thanks for your valuable time and support, If I do it manually it will take days to complete the work but formula will help me to save time.
 
Shabbo
As I tried to explain ...
Try to find out something same as You could do it manually!
Is there any clear rule - which part is that 'reference number'?
... You've asked many times about these.
If there is not any clear rule
... then as I also tried to write
> Otherwise ... could You do it opposite way?
> You have 'only reference number' ... You could use that to find it from 'full Narration'.
 
Shabbo
As I tried to explain ...
Try to find out something same as You could do it manually!
Is there any clear rule - which part is that 'reference number'?
... You've asked many times about these.
If there is not any clear rule
... then as I also tried to write
> Otherwise ... could You do it opposite way?
> You have 'only reference number' ... You could use that to find it from 'full Narration'.
Dear sir,
There any formula which will search/ find or pull out numbers available in bank ledger.
 
Shabbo
Sure ...
but I would solve Your case as I wrote ... and ... 'my way'
Press [ Do It ]
Ps. No matter, how many rows You would have ... keep same layout.
Ps2 ... actually, You might only need to find out differences ... then no need to compare those values manually
 

Attachments

  • wildcard.xlsb
    19.3 KB · Views: 5
Last edited:
Following can be one formula approach. In cell F2 implement following formula
=LOOKUP(2^15,SEARCH('Bank Ledger'!$E$2:$E$4,'Bank Statement'!C2,1),'Bank Ledger'!$F$2:$F$4)
Copy down.

Note that the results in the third cell won't match as in one cell is having "R52019040272121945" and "R5201904027212194500" where the leading zeroes cause mismatch!
 
Following can be one formula approach. In cell F2 implement following formula
=LOOKUP(2^15,SEARCH('Bank Ledger'!$E$2:$E$4,'Bank Statement'!C2,1),'Bank Ledger'!$F$2:$F$4)
Copy down.

Note that the results in the third cell won't match as in one cell is having "R52019040272121945" and "R5201904027212194500" where the leading zeroes cause mismatch!
Thank you so much sir.
 
Shabbo
Sure ...
but I would solve Your case as I wrote ... and ... 'my way'
Press [ Do It ]
Ps. No matter, how many rows You would have ... keep same layout.
Ps2 ... actually, You might only need to find out differences ... then no need to compare those values manually
Thank you so much sir.
 
Back
Top