Formula Help... Vlookup, Index, Match, Collect?

JakeMote

I was hoping to get some help building a formula to accomplish looking at 3 different variables and then matching that up on a table to give the solution.

I want to have a formula that populates the "plant ID number" when it looks up the "plant", "mix type", and "RAP Content". I have these listed in a table on a separate sheet for it to reference (second picture). So for instance the first row in the first picture the plant ID Number should be 241453 based on the fact the plant is "coffee lake" the mix type is "lvl 3 1/2" ODOT" and the RAP content is "30%".

Hope this all makes sense.

Kelly Moore

Hey @JakeMote

I suggest you add a helper column to BOTH sheets where you JOIN the three pieces of data. The data must be joined in the same order so that the resulting text strings will be a match.

To Join the three columns you just use a + between the fields. Do this on each sheet in the helper column

=Location@row+[Mix Description]@row+[Rap Content]@row

If you wanted a separator in between each data piece, it would be (assuming a hyphen)

=Location@row+"-"+[Mix Description]@row+"-"+[Rap Content]@row

After creating these Joined helper columns, this formula will go inside your sheet2 Plant ID Number field.

=INDEX({sheet1 Mix ID column}, MATCH([sheet2 Joined Helper column]@row, {sheet1 Joined Helper column},0))

Will this work for you?

Kelly

Kelly Moore

Hey @JakeMote

I suggest you add a helper column to BOTH sheets where you JOIN the three pieces of data. The data must be joined in the same order so that the resulting text strings will be a match.

To Join the three columns you just use a + between the fields. Do this on each sheet in the helper column

=Location@row+[Mix Description]@row+[Rap Content]@row

If you wanted a separator in between each data piece, it would be (assuming a hyphen)

=Location@row+"-"+[Mix Description]@row+"-"+[Rap Content]@row

After creating these Joined helper columns, this formula will go inside your sheet2 Plant ID Number field.

=INDEX({sheet1 Mix ID column}, MATCH([sheet2 Joined Helper column]@row, {sheet1 Joined Helper column},0))

Will this work for you?

Kelly

JakeMote

Kelly,

This worked well thank you for the advise!

Thanks