How Do You Write An If Then Formula In Excel Excel Tip – Use VLOOKUP to Return Multiple Values Horizontally

You are searching about How Do You Write An If Then Formula In Excel, today we will share with you article about How Do You Write An If Then Formula In Excel was compiled and edited by our team from many sources on the internet. Hope this article on the topic How Do You Write An If Then Formula In Excel is useful to you.

Excel Tip – Use VLOOKUP to Return Multiple Values Horizontally

I have been asked this questions a number of time about Excel and VLOOKUP formulas. How to return multiple values from one VLOOKUP.

Let’s say we have customers who have more than one piece of equipment, but we want to return the serial numbers of all those pieces of equipment using the VLOOKUP formula. Normally customers have one item that we want to match, so a normal VLOOKUP fits our needs but some have more than one. We need to do a bit more with our VLOOKUP and turn up the volumes on this formula.

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

Customer Number Equipment Type Serial Number

776546 Baffle 1432

776546 Stoker 1552

776546 Plank 1749

776324 Plank 1500

764632 Baffle 1683

763452 Baffle 1815

763543 Stoker 1263

OK, so looking up the multiple types or serial numbers is really easy in this example with such a small data set, but what about if we have thousands of records, that would be pretty tough. Let’s get Excel to do the tough work, and write a formula for get this job done.

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

Firstly this is an array formula. An Array formula is a formula that will perform multiple calculations on one or more sets of values). The INDEX function looks in our table ($A$2:$C$8)

  • It then uses the SMALL function to find which row to look in 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 considers the first row of our table as row 1. Because our table data starts in row 2, we need to subtract 1 from the ROW value to get the correct row inside 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 in column 3, which is where the data is found.
  • As we drag the formula along the columns, the next instance of the row number I.e 2 in F3 and 3 in G3 and so on until there are no more matches and we get an error. Job done.

Video about How Do You Write An If Then Formula In Excel

You can see more content about How Do You Write An If Then Formula In Excel on our youtube channel: Click Here

Question about How Do You Write An If Then Formula In Excel

If you have any questions about How Do You Write An If Then Formula In Excel, please let us know, all your questions or suggestions will help us improve in the following articles!

The article How Do You Write An If Then Formula In Excel was compiled by me and my team from many sources. If you find the article How Do You Write An If Then Formula In Excel helpful to you, please support the team Like or Share!

Rate Articles How Do You Write An If Then Formula In Excel

Rate: 4-5 stars
Ratings: 9475
Views: 24709020

Search keywords How Do You Write An If Then Formula In Excel

How Do You Write An If Then Formula In Excel
way How Do You Write An If Then Formula In Excel
tutorial How Do You Write An If Then Formula In Excel
How Do You Write An If Then Formula In Excel free
#Excel #Tip #VLOOKUP #Return #Multiple #Values #Horizontally

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

Bài viết đã được tạo 1408

Bài liên quan

Bắt đầu nhập từ khoá bên trên và nhấp enter để tìm kiếm. Nhấn ESC để huỷ.

Trở lên trên