If cell matches cell in range of cells matches input data from other cell

Copper Contributor

I need a formula that will check a cell value against a range of cells and if that cell value shows up in that range I want the cell to contain the data from another cell.  I provided a link to my test spreadsheet for your reference.  What I am trying to do is if a cell under column G (Account Name) in the All Accounts sheet matches any column in the range of cells in column A (End customer name) in the Product Type sheet, then show the content of the cell in column B (Product) in the Product Type sheet in the cell under column F (Main UC Product Type) in the All Accounts sheet.

 

So, if the Account Name "Test" in the All Accounts sheet shows up anywhere in column A (End customer name) in the Product Type sheet then show the content of column B (Product) in the Product Type sheet in column F (Main UC Product Type) in the All Accounts sheet.  In the example spreadsheet the Account Name "Test" shows up on both sheets so I want the Main UC Product Type cell in column F in the All Accounts sheet to show "Contact Center."

 

I'm sure I have repeated myself and hope I am not confusing you!  Please reply with any advice you have to get this done.

 

Here is the link to my test file . . . 

 

https://www.dropbox.com/scl/fi/bga2o0cekhuzvrhlaby65/Customer-match-to-product-type-test-file.xlsx?d... 

1 Reply

@mamerman 

 

Please try this...

 

On All Accounts Sheet,

In F2...

=IFERROR(INDEX('Product Type'!B:B,MATCH(G2,'Product Type'!A:A,0)),"")

and copy it down.