VLookUp or Index/Match

[aromaveda]

Hi All,

I am working on this complex project where I need to get values from data 2 sheet (Qty) and fill them on Data 1 in Qty column. Please refer to the attached workbook where
1) Data 1 is the report format & I need to fill values in Qty column from Data 2 Qty.
2) The complexity is variation in item numbers on data 1 & data 2 which needs to be matched. In instances where the item numbers are separated by / I need to capture either the item number starts with 917 or after / (which is 231XXX) and enter the qty.
3) If item number on data sheet is in the format 5400:5200:5201 then i need to match the last segment i.e 5201 with Data 1 either one of the columns com 1 item # or comp 2 item # and then enter the qty in qty column.

How can i acieve this with lookup or index/match formulas?? I am not sure if I explained it well or confused you but as i mentioned some how by matching etiher of the item numbers i need to get the qty filled in.

Please help

Thanks
SD

[aromaveda]

Donkey,

Can you help?

thanks
SD

[aromaveda]

Hi All,

I have 3 columns with 3 differen item numbers for one item only & I want to V-look up using either one of it (whichever matches) within a string of numbers that has combination of either two or 3 of these
columns. They are either separated by a /. For example
Sheet # 1
A B C
0917413 220673 501

Sheet 2
A
0917413/220673/501

Somebody please help
Thanks
SD

[Shijesh Kumar]

suppose the value to be searched is in cell A1

then

Please Login or Register  to view this content.

[aromaveda]

Shijesh,

Thanks for the help, but I am getting error message. what if in some cases there are two alpha letters before the item code on sheet 2? Do you think the error message is due to that? Could you please suggest something for that?
Thanks
Sanjay

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[aromaveda]

Donkey,
Thanks, plz see attached two sheets. 1st tab shows the format that i need to fill in the qty numbers in August mont (last) column. The data needs to be captured is from 2nd sheet under the Qty column.

Thanks
Sanjay

[DonkeyOte]

Sanjay, as I see it this thread is directly related to:

http://www.excelforum.com/excel-gene...dex-match.html

As such I have merged the two - going forward please do not create multiple threads on the same topic.

[DonkeyOte]

It might be worth putting in the expected results on your sample file based on the sample raw data... this way people can be sure they are replicating your logic.

My own advice would probably be to adjust your formulae in B on Sheet2 such that you get a clearly delimited list of references (currently as I see it the formula is not returning the correct data in all cases)

Please Login or Register  to view this content.

You can then "possibly" use a SUMIF approach with wildcard, based on your existing formulae on DATA1 sheet

Please Login or Register  to view this content.

But as inferred earlier the above is a "guess" as there are no expected results so it's more than likely the above is missing some key logic requirement.