Using Index(Match) to populate list

[erice]

Hello,

I have workbook with two different tabs for material. On the Matl tab the user would enter the material's part number that they are wanting to use. This value in column A is then used elsewhere. I have added a second tab, Matl2, that I am wanting to automatically populate values in column A if they are entered on the Matl tab. I was able to get this to work using an index match for some of the material numbers. The ones I am having trouble with are the materials in column B with a "-##" behind them. For instance, on row 18 I have a 702032 in column A of the Matl tab. I am wanting that same 702032 to be populated on the Matl2 tab. I have attached the sheet I am working with to help. There are usually twice as many materials on the Matl tab because we have different pricing for different locations.

Thank you

[CAntosh]

I don't know if I'm understanding the goal correctly, but try the following in A4 of sheet2:

=IFERROR(IF(INDEX(Chosen_Steel,MATCH(B4,Steel_Part,0))=0,"",INDEX(Chosen_Steel,MATCH(B4,Steel_Part,0))),"")

...then fill down. Does that do the trick?

[erice]

CAntosh,

thank you for your reply. That appears to be working like I was hoping for.

Thanks again!

[CAntosh]

Glad to help, good luck!

[erice]

I thought this was working, but when I went to add it to the full scale sheet I noticed a problem. In the workbook attached the user will enter the material number in column A for the facility that it would be coming from. The available materials to pick from at Facility 1 (rows 5 to 25) and Facility 2 (rows 27 to 47) are the same in column B, but have different pricing due to coming from different locations. On the Matl2 tab I am wanting column A to populate if that material was entered on the Matl tab. This is working for all materials coming from Facility 1, but when I enter anything in the Facility 2 section it does not populate on the Matl2 tab. For instance, A10 on the Matl2 tab should read 702008 if the formulas were working correctly.

[CAntosh]

hmmm... I think I see what you mean. It was only looking at the first occurrence of the matching B value on sheet one. Try this version:

On Matl2, A4: =IF(COUNTIFS(Chosen_Steel,IFERROR(LEFT(Matl2!B4,FIND("-",Matl2!B4)-1),B4),Steel_Part,Matl2!B4)>0,IFERROR(LEFT(Matl2!B4,FIND("-",Matl2!B4)-1),B4),"")

It should return a match if there are any matches on sheet1 for the col. B value that also offer the pre-hyphen value in col A. Take a look:

[erice]

CAntosh,

That appears to be working properly now. I will put that formula into my full scale workbook and have a few people trial it for errors on Monday. Thanks again for your quick replies!

[CAntosh]

Excellent, good luck!