How to find product details by entering its last 4 digit only

[devawad]

Dear All,

I have data in which Customer wise product code its full name and its rate is mentioned, I want if I enter only last 4 digit of that product code in Cell B6, so based on A6 reference (in which customer name is given) excel should give result in C6 full item code,D6 full item name,and E6 rate of the product.

Here I have attached sample excel sheet for reference.

Regards,

Dev.

[samba_ravi]

Try like
c4=INDEX('sale data'!$E$2:$E$15,MATCH(1,INDEX((RIGHT('sale data'!$D$2:$D$15,4)=B4&"")+0,0),0))

[avk]

I have add * before & after data in "B4"
In "C4"

Please Login or Register  to view this content.

In "D4"

Please Login or Register  to view this content.

In "E4"

Please Login or Register  to view this content.

For more clarity file attach.

[devawad]

Dear AVK,

In cell C4 and D4 formulas are working perfectly but in E4 price should change as the customer name change in cell A4 which not happening also there are two different price for the same customer for the same product, here latest(most recent)date rate should come there plz help.

Dev.

[devawad]

Dear nflsales,

Your formula also working great in D4, if it is possible plz send formulas for the remaining things.


Dev.

[avk]

increase range in "E4"

Please Login or Register  to view this content.

[shukla.ankur281190]

Try

C4

Please Login or Register  to view this content.

or

C4

Please Login or Register  to view this content.

Drag towards the cell

[devawad]

shukla.ankur281190

Sir, your formulas are working great,only problem is in cell E4 it is not taking the most latest date invoice price. when there is same item and different price
plz guide on the same.


Dev.

[shukla.ankur281190]

What is your expecting result in E4???

[devawad]

Dear shukla.ankur281190,

In E4 , if in data,same item has different price on different date,for example if item 25-01001 has 100 Rs price in 20th April invoice and the same item 25-01001 has 90 Rs price in 21st April invoice so result should be 90 Rs because it is latest rate given to customer.

Attached sheet for your reference.

Thanks

Dev

[shukla.ankur281190]

Try

E4

Please Login or Register  to view this content.

[devawad]

Dear shukla.ankur281190,

Its just fantastic, your genius and great sir , now it is working as I want, your a master

Thanks

Dev.

[shukla.ankur281190]

Glad it helps you. please add reputation by clicking star * icon left corner on solution post.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.