INDEX MATCH to OTHER WORKSHEET

SupplyChain

Hello,

I'm evaluating the use of Forms for a "field ordering" system from an Item Master Table. Best scenario is to create a form to request multiple items via a single submission (not multiple items in a single cell = less desirable data management), and also to have a confirmation submission email sent to an email address other than the user submitting the form. To my knowledge these aren't quite an option, so the solutions I've found lead me to the following:

When filling out the form, since there's no auto-fill based on other entries to a unique identifier (ie: if Item Description entered is "1-1/2" BIT", then Item Number auto-fills with B15), it makes the most sense to have someone search/order by item name rather than item number. 

So, I want to include the Item# in the form results with a formula. Originally I was going to do a VLOOKUP, but realized the Item Number is to the left of the ITEM DESCRIPTION in the master table. 

In short, I need to figure out how to do an INDEX/MATCH to another sheet to use the ITEM DESCRIPTION to retrieve the corresponding ITEM NUMBER. 

Thank you in advance for your time and expertise!

 

Paul Newcome

INDEX:

 

=INDEX(range to pull from, row number, column number)

 

row and column numbers within the INDEX function need to be numerical values.

.

MATCH:

 

=MATCH(text to search for, range to search in, sort order)

 

This will generate a numerical value based on where within a range the first instance of the specified text is found. If that range is a single column, then it will return the row number.

.

We will use the MATCH function to generate the numerical value needed for the INDEX function's row number. Since we are looking at single columns for our ranges, we do not need to specify a column number.

.

=INDEX({Other Sheet CDS Item Number Column}, MATCH([Item Description Search]@row, {Other Sheet Description Column}, 0))

SupplyChain

Hi Paul,

Thank you very much. I was looking at some of the other posts about the very same topic and couldn't quite get it right. I think part of my problem I overlooked is that I was typing { before selecting the other references, which adds another { by default when inserting the reference, therefore confusing the logic in the formula. 

 

Thanks again.  

 

Paul Newcome

Happy to help!