Formula required to match a value in sheet 1 to sheet 2 Column A and select a value from B

**deputydoug** · 04-09-2015, 01:32 PM

Hi,

Hopefully somebody can help with this currently I'm using the following formula:

=IF(Sheet1!C3=Sheet2!$A$2,Sheet2!$B$2,IF(Sheet1!C3=Sheet2!$A$3,Sheet2!$B$3,))

I have 3 sheets in total and I use this formula to populate sheet 3 with products from our suppliers spreadsheet so Sheet 1 has a list of product types and in sheet 2 there is a list of the product types in column A and the relevant categories in column B.

I use the formula to convert the product types into the relevant categories automatically the problem I have is that you can only repeat this forumula around sixty times and we have over 100 product types so I'm wondering if there is a quick way to do this. So what I'm looking for is the following:

A formula to use in sheet 3 A1 to Match the value in Sheet 1 A1 with Values in Sheet 2 A1:A300 If A1 matches the value in Sheet 2 A99 it will populate the value into sheet 3 A1

Hopefully this is a bit clearer than mud any questions let me know thanks for your help

**mcmahobt** · 04-09-2015, 02:32 PM

It may be easier to visualize the problem if you upload a sample workbook with your expected results included. To upload, you select Go Advanced --> Manage Attachments below.

**deputydoug** · 04-10-2015, 02:03 AM

Template.xlsx

Thanks please see attached file.

**mcmahobt** · 04-10-2015, 08:07 AM

Unfortunately I'm not able to follow your query above because you reference "Sheet1, 2, etc.", but the sheets are named. Could you substitute in the sheet names for clarity?

**benishiryo** · 04-10-2015, 08:24 AM

welcome to the forum, deputydoug. i admire your patience for doing such a long formula!

back at the question; people normally do such a thing with only 2 sheets involved. that should be your Category_Converter sheet & Categories sheet. you can use VLOOKUP. your use of the 3rd sheet is a little weird, but we'll get to that. try doing this formula in row 2 of Category_Converter sheet at the moment:
=VLOOKUP(C2,Categories!A:B,2,0)

this means to look for C2 ("Built in Shower Valve") under Categories worksheet column A. it should include column B too. because when C2 is found, you want to return the 2nd column value. the 0 at the back of the formula is to find an exact match.

now... back to the 3rd sheet. how do you know which category to look for? if you are simply taking from row 2 of Category_Converter sheet, and later row 3 & so on, why not do the formula in Category_Converter sheet instead?

**deputydoug** · 04-10-2015, 08:44 AM

I'm a very patient person I knew there would be a fast way to do it.
I've done as suggested and put that into Category_Converter sheet

Problem solved thanks for your help that's worked perfectly.

Formula required to match a value in sheet 1 to sheet 2 Column A and select a value from B

LinkBack

Thread Tools

Rate This Thread

Display

Formula required to match a value in sheet 1 to sheet 2 Column A and select a value from B

Re: Formula required to match a value in sheet 1 to sheet 2 Column A and select a value fr

Re: Formula required to match a value in sheet 1 to sheet 2 Column A and select a value fr

Re: Formula required to match a value in sheet 1 to sheet 2 Column A and select a value fr

Re: Formula required to match a value in sheet 1 to sheet 2 Column A and select a value fr

Re: Formula required to match a value in sheet 1 to sheet 2 Column A and select a value fr

Thread Information

Users Browsing this Thread

Similar Threads

(Urgent Req) VBA code required to select a sheet just before a active sheet

Copy select cells from one sheet into the next available column in another sheet

Copy select cells from one sheet into the next available column in another sheet

Select data in Sheet 2 based on column values in Sheet 1

Match column from sheet 2 to sheet 1 and copy rows if match exists.

Bookmarks

Bookmarks

Posting Permissions