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

Entry number to find.

S

Shabbo

Member
Dear Sir,

I have advance paid sheet and another one is entry book.

I wanted to know entry number against the vehicle number.

Vehicle numbers are repeated but it need be consider vehicle number and date as Vehicle number MH06 AC 1210 on dated 03/03/2017 then I found entry number manually M161704983.
 

Attachments

  • Entry Book.xls
    72.5 KB · Views: 5
vletm said:
Shabbo
Something like this?
Click to expand...
Dear Sir,

Entry number will be one only for one date eg : for Vehicle no - MH04 HD 8337 dated 03/03/2017 entry no will be M161704985

Entry no : M161705011 will be for dated 4th Mar 2017 in front of Vehicle no : MH04 HD 8337.
 
You can do this by creating two helper columns and VLOOKUP.

Step1: Create a Lookup Key by concatenating two required fields i.e. Vehicle Number & Date on both the sheets.
Step2: Use VLOOKUP to get the results.

See attached file.
See attached file.
See attached file.
 

Attachments

  • Entry Book (2).xls
    86 KB · Views: 7
Dear Shabbo, Firstly insert table in sheet "Entry Book". This is for if infuture you need add more data entry, then you can not change formula range in "Advance Paid" sheet.
Secondly, in sheet "Advance Paid" kindly remove all blank row and you mentioning header name by date, instead of that header name mentioned in row first only, and from "A2" freez panes. When you scroll down first row freez.

instead of vlookup, i am using index match formula.
Look attach file.
 

Attachments

  • Entry Book.xls
    91.5 KB · Views: 4
Why Excel's INDEX MATCH is better than VLOOKUP
When deciding which formula to use for vertical lookups, the majority of Excel gurus agree that INDEX / MATCH is far better than VLOOKUP. However, many Excel users still resort to utilizing VLOOKUP because it's a simpler function. This happens because very few people fully understand all the benefits of switching from Vlookup to Index Match, and without such understanding no one is willing to invest their time to learn a more complex formula.

Below, I will try to point out the key advantages of using MATCH / INDEX in Excel, and then you decide whether you'd rather stick with Vlookup or switch to Index Match.
4 top benefits of using MATCH INDEX in Excel
Right to left lookup.
nsert or delete columns safely.
No limit for a lookup value's size.
Higher processing speed.
 
Shabbo
Okay, now it needs both 'date' and 'vehicle no.'!
I didn't change basic idea of function!
Of course You could add that feature too or how?

CA Rohit
'Get_ENos' is almost 'normal' VBA-code.
Find somewhere, how to to make 'own functions' and start to do those.
 

Attachments

  • Entry Book.xls
    107.5 KB · Views: 1
AVK said:
Why Excel's INDEX MATCH is better than VLOOKUP
When deciding which formula to use for vertical lookups, the majority of Excel gurus agree that INDEX / MATCH is far better than VLOOKUP. However, many Excel users still resort to utilizing VLOOKUP because it's a simpler function. This happens because very few people fully understand all the benefits of switching from Vlookup to Index Match, and without such understanding no one is willing to invest their time to learn a more complex formula.

Below, I will try to point out the key advantages of using MATCH / INDEX in Excel, and then you decide whether you'd rather stick with Vlookup or switch to Index Match.
4 top benefits of using MATCH INDEX in Excel
Right to left lookup.
nsert or delete columns safely.
No limit for a lookup value's size.
Higher processing speed.
Click to expand...
Agree. These advantages are known to me. However, not all users have the same level of proficiency. Some people may actually struggle with it. On smaller scale, there's no real performance distinction between formulas. Just the need to get a working solution is important.

To me, ease of implementing a concept to get solution is more important for some users. If there's performance benefit then it is even better.

e.g. following construct will give the same results without helper column but might be difficult for some people to implement if layout changes:
=LOOKUP(2,SEARCH(SUBSTITUTE(G3," ","")&A3,SUBSTITUTE('Entry book'!$H$1:$H$87," ","")&'Entry book'!$B$1:$B$87,1),'Entry book'!$A$1:$A$87)

Hope this clarifies.
 
You must log in or register to reply here.
Back
Top