Excel Array Formula Generally No Need To Use If Function Excel Tip – Use VLOOKUP to Return Multiple Values Horizontally

You are searching about Excel Array Formula Generally No Need To Use If Function, today we will share with you article about Excel Array Formula Generally No Need To Use If Function was compiled and edited by our team from many sources on the internet. Hope this article on the topic Excel Array Formula Generally No Need To Use If Function is useful to you.

Excel Tip – Use VLOOKUP to Return Multiple Values Horizontally

I have been asked these questions many times about Excel and VLOOKUP formulas. How to return multiple values ​​from a VLOOKUP.

Let’s say we have customers with more than one device, but we want to return the serial numbers of all those devices using the VLOOKUP formula. Usually customers have one item we want to match, so a simple VLOOKUP fits our needs but some have more than one. We need to do a little more with our VLOOKUP and open the volumes in this formula.

So, let’s do this with some data… Here’s a list of customers and their devices (a very small extract of our data set).

Customer Number Equipment Type Serial Number

776546 Baffle 1432

776546 Stoker 1552

776546 Table 1749

776324 Plank 1500

764632 Baffle 1683

763452 Baffle 1815

763543 Stoker 1263

Ok, so finding multiple types or serial numbers is really easy in this example with such a small data set, but what if we have thousands of records, that would be very difficult. Let’s get Excel to do the hard work, and write a formula to make it work.

=INDEX($A$2:$C$8,SMALL(IF($A$2:$A$8=$E$2,ROW($A$2:$A$8)-1), COLUMNS($F2:F2)) ,3)

First it is an array formula. The array formula is a formula which will perform multiple calculations on one or more sets of values). The INDEX function appears on our table ($A$2:$C$8)

  • It then uses the SMALL function to figure out which row to look at and then subtracts 1 from what SMALL tells us. (It takes the smallest row number first and matches the value in column C)
  • The ROW function returns the row in the worksheet where our value was found.
  • The INDEX function assumes row 1 is the first row of our table. Because our table data starts at row 2, we must subtract 1 from the ROW value to get the correct row within the table.
  • In your own spreadsheet you may need to adjust this value to take account of where your data table is located
  • The INDEX function then looks at column 3, where the data is found.
  • As we drag the formula across the columns, another instance of row number i.e. 2 in F3 and 3 in G3 and so on until there is no match and we get an error. job done

Video about Excel Array Formula Generally No Need To Use If Function

You can see more content about Excel Array Formula Generally No Need To Use If Function on our youtube channel: Click Here

Question about Excel Array Formula Generally No Need To Use If Function

If you have any questions about Excel Array Formula Generally No Need To Use If Function, please let us know, all your questions or suggestions will help us improve in the following articles!

The article Excel Array Formula Generally No Need To Use If Function was compiled by me and my team from many sources. If you find the article Excel Array Formula Generally No Need To Use If Function helpful to you, please support the team Like or Share!

Rate Articles Excel Array Formula Generally No Need To Use If Function

Rate: 4-5 stars
Ratings: 9272
Views: 93447507

Search keywords Excel Array Formula Generally No Need To Use If Function

Excel Array Formula Generally No Need To Use If Function
way Excel Array Formula Generally No Need To Use If Function
tutorial Excel Array Formula Generally No Need To Use If Function
Excel Array Formula Generally No Need To Use If Function free
#Excel #Tip #VLOOKUP #Return #Multiple #Values #Horizontally

Source: https://ezinearticles.com/?Excel-Tip—Use-VLOOKUP-to-Return-Multiple-Values-Horizontally&id=9075152