{"id":906,"date":"2023-06-21T11:11:25","date_gmt":"2023-06-21T03:11:25","guid":{"rendered":"https:\/\/www.info-trek.com\/blog\/?p=906"},"modified":"2024-05-02T12:09:16","modified_gmt":"2024-05-02T04:09:16","slug":"how-to-return-multiple-return-using-one-id-with-excel-part-1","status":"publish","type":"post","link":"https:\/\/www.info-trek.com\/blog\/how-to-return-multiple-return-using-one-id-with-excel-part-1\/","title":{"rendered":"How to return multiple return using one ID with EXCEL Part.1"},"content":{"rendered":"\n

I often received many requests from my student asking me \u201cAnne, how can I return multiple answer by using one identifier?\u201d. This usually happened when there are trying to look for the activity of a particular identifier (ID) and in fact I usually will came out with the solution like you can do it using pivot table, but some people will prefer to get it done by using Excel formula. Well, I always like to provide\u2019em with different options, so they get to try around to see which work best for them. I believe different people has different way of them working with Excel, nothing\u2019s wrong or right about whichever way, it totally depends on individual preference.<\/p>\n\n\n\n

Return multiple output using Excel Formula<\/h3>\n\n\n\n

In order to achieve as stated, you will be needing helps from a few Excel functions. There is no such straight forward solution to that. In the end, by having all these functions being joint together will then formed a nested function. Not just nested function, but a nested array function. The functions are IF(), ROWS(), SMALL(), IFERROR(), INDEX() and MATCH(). It\u2019s cray cray\u2026 I know. So, if you\u2019re still interested, continue reading.<\/p>\n\n\n\n

Let me explain to you the example I have as below.<\/p>\n\n\n\n

The first range contains all the purchase activity from our customer. In each line, it stated which customer (by using customer ID) and when did they make the purchase plus how much they spend on that particular purchased.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

So right now, I would like to list down the activity of the customer by using their ID.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Once they CustomerID is inserted into the cell, the transaction will be listed down at the right bottom section of the screenshot as shown above. Which means the formula will be inserted at the area where the output is going to be. Now let\u2019s get going.<\/p>\n\n\n\n

Functions and its usage<\/h4>\n\n\n\n

In this section of the article I\u2019m going to cover the functions being used in this exercise and also when where you\u2019re going to need them. Of course these functions has many different usage based of different context. I won\u2019t be covering it all in this series. Simply just to give you a rough idea of those functions.<\/p>\n\n\n\n

ROWS()<\/strong><\/p>\n\n\n\n

Syntax<\/strong><\/td>ROWS(Array)<\/td><\/tr>
Summary<\/strong><\/td>Returns the count of rows of the given reference<\/td><\/tr>
How to<\/strong><\/td>ROWS(A3:A9) => 7 (7 cells within the reference)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Example<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

ROW()<\/strong><\/p>\n\n\n\n

Syntax<\/strong><\/td>ROW([Reference])<\/td><\/tr>
Summary<\/strong><\/td>Returns the row number of a reference<\/td><\/tr>
How to<\/strong><\/td>ROWS(A4) => 4 (the reference it located at the 4th<\/sup> row of the sheet)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Example<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

IF()<\/strong><\/p>\n\n\n\n

Syntax<\/strong><\/td>IF(Logical_Test, Value_If_True, Value_If_False)<\/td><\/tr>
Summary    <\/strong><\/td>Return TRUE value or FALSE value from a logical test.
Logical Test is when any test conduct using any comparison operators.
When the test returned a TRUE\/False result, user get to specify what happen or what to return as the output when it is TRUE\/FALSE<\/td><\/tr>
How to<\/strong><\/td>=IF(A3>35,\u201d Expensive\u201d, \u201cAffordable\u201d)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Example<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

SMALL()<\/strong><\/p>\n\n\n\n

Syntax<\/strong><\/td>SMALL(Array, K)<\/td><\/tr>
Summary    <\/strong><\/td>Return the smallest number by its rank<\/td><\/tr>
How to<\/strong><\/td>=SMALL(A3:A9,2) => return the 2nd <\/sup>smallest number within the array<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Example<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

IFERROR()<\/strong><\/p>\n\n\n\n

Syntax<\/strong><\/td>=IFERROR(Value, If_Error_Value)<\/td><\/tr>
Summary              <\/strong><\/td>This function specifically handles all sort of error possibility.
The example below shows the % difference from last year.
#DIV\/0! will be returned when This Year value is 0 or no value<\/td><\/tr>
How to<\/strong><\/td>=IF(A3\/B3,\u201dNo Value\u201d)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Example<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

MATCH()<\/strong><\/p>\n\n\n\n

Syntax<\/strong><\/td>=MATCH(Lookup_Value,Array, [Match_Type])<\/td><\/tr>
Summary   <\/strong><\/td>Match function is used to locate the position of a lookup value within a row, a column or a table. The position returned in a formed of index.<\/td><\/tr>
How to<\/strong><\/td>=MATCH(2797,A3:A9,0)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Example<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

INDEX()<\/strong><\/p>\n\n\n\n

Syntax<\/strong><\/td>=INDEX(Array, Row_Num, [Column_Num])<\/td><\/tr>
Summary     <\/strong><\/td>Index function will return a value from a column, row or a table based on the coordinate or position index.<\/td><\/tr>
How to<\/strong><\/td>=INDEX(A3,A9,6,0)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Example<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Step 1: Convert range into a Table object<\/h3>\n\n\n\n

As we know the transaction of the purchase will grow and expand as soon there are more activity from the customers. Which also means, as soon there are more purchases, the line of the transaction will grow. Converting it into a table is to ensure whenever the activity is added to the range, the Table will expand. And as the Table has expanded, the formula will automatically lookup from the latest size of the table. This is to also make sure the search is always keeping up with the latest transaction.<\/p>\n\n\n\n

To convert to table, you can locate the \u201cInsert Table\u201d command from either Home Tab, Insert Tab or using a keystroke. But before that, you must first locate your cursor in within the region of the transaction.<\/p>\n\n\n\n

3 different methods of creating Table Object<\/strong><\/p>\n\n\n\n

Home Tab<\/em><\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Insert Tab<\/em><\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

*You must be curious why does the name of the command is different, GOD knows why. Neither do I have any idea, but anyways\u2026yea. That\u2019s the button that allows you to convert te range into a Table object.<\/p>\n\n\n\n

Ctrl + Tt<\/strong><\/p>\n\n\n\n

To convert the range into a table object, you must make sure your cursor is being in one of the cell of the range, and then apply your keystroke (Ctrl + T) Either of the option you took from above, you will immediate prompted the dialog box \u201ccreate table\u201d after you trigger the command. Before selecting OK, check the range and the header checkbox. If everything is good, proceed with OK.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Step 2: Renaming Table object<\/h3>\n\n\n\n

Immediately change the name of the table from the default name like \u201cTable#\u201d into something more relevant and mean have changed the table name from \u201cTable1\u201d to \u201cpaid\u201d.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Step 3: Begins with the extraction<\/h3>\n\n\n\n

Follow the tutorial below to extract the formula. I will be doing the explanation later on.<\/p>\n\n\n\n

\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

I often received many requests from my student asking me \u201cAnne, how can I return multiple answer by using one identifier?\u201d. This usually happened when there are trying to look for the activity of a particular identifier (ID) and in fact I usually will came out with the solution like you can do it using […]<\/p>\n","protected":false},"author":4,"featured_media":162,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-906","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/906","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/comments?post=906"}],"version-history":[{"count":1,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/906\/revisions"}],"predecessor-version":[{"id":907,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/906\/revisions\/907"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/162"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=906"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=906"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=906"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}