Ask Your Question

populate sheet with value from another sheet [closed]

asked 2013-10-21 18:50:09 +0200

ValleyJim gravatar image

I have two sheets, one containing categories the other containing products.

Example for sheet1 - Contains category and cat number Widgets 1 Red Widgets 2 Blue Widgets 3

Sheet2 - Contains products and product category 1oz Widget 1 2 3 2oz Widget 4 5 6

In this example the 1oz widget belongs to category 1, 2 and 3 the 2oz widget belongs to category 4, 5 and 6. What I would like to do is replace the category number in sheet 2 with the text. So 1oz widget would become 1oz Widget - Widgets

The description and numbers are in seperate columns. Sheet 2 contains up to 3 categories for each product.

So what I would like to do is replace the category number in the sheet2 with the name in category 1.

Any help would be greatly appreciated because this will save me a LOT of time.

Thanks for reading! Jim

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-12 06:30:16.108196

2 Answers

Sort by » oldest newest most voted

answered 2013-10-21 22:00:35 +0200

m.a.riosv gravatar image

I think the easy is use the VLOOKUP() function:

=VLOOKUP(SearchCriterion; Array; Index; SortOrder)
SearchCriterion is the value searched for in the first column of the array.
Array is the reference, which is to comprise at least two columns.
Index is the number of the column in the array that contains the value to be returned. The first column has the number 1.
SortOrder is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE or zero if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.

You can find it in the inner help, read carefully, take special attention to the SortOrder parameter in your case I think must be False or zero, to avoid errors.

edit flag offensive delete link more

answered 2013-10-23 00:39:56 +0200

ValleyJim gravatar image

Thank you for your reply.

This worked perfectly. That was a great help, I learned a lot and saved a lot of time.

Thanks for the help! Jim

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-10-21 18:50:09 +0200

Seen: 522 times

Last updated: Oct 23 '13