Excel/extract data


QUESTION: Hello Tom,
trying to extract data.    
A      B          C
SAles   PROPERTY          FEES
SL   33 The Watergardens    3,510.00
SL   15 Epad Apartment     1,237.60
AS   120 Hallmark Court    1,274.00
AS   234 Berglen Court    1,872.00
SL   2810 Landmark West    5,148.00

What I get is blank lines in between where the initials SL is not found using =IF(A3=$R$2,B3,"")and =IF(Q4="","",F3)to bring across the address and the total fees

33 The Watergardens    3,510.00
15 Epad Apartment     1,237.60
2810 Landmark West    5,148.00
32 Victoria Wharf    1,456.00
Your help as always appreciated.



if you want them contiguously listed you would need to use an array formula

Where you show 33 The Watergardens you would put

R3:  =INDEX($B:$B,SMALL(IF($A$3:$A$50=$R$2,ROW($A$3:$A$50)),ROW(A1)),1)
This must be entered with Ctrl+Shift+Enter rather than just enter since it is an array formula

in S3: =if(R3="","",Vlookup(R3,$B:$C,2,False))

If you have excel 2007 or later in R3:
This must be entered with Ctrl+Shift+Enter rather than just enter since it is an array formula

Now select R3:S3 and drag fill down until you run out of data.

all formulas were tested and worked for me to produce:

33_The_Watergardens   3510
15_Epad_Apartment   1237.6
2810_Landmark_West   5148

(I didn't format the amounts to show the pound)
I put in underscores so I could use text to columns to enter your test data.

Tom Ogilvy

[an error occurred while processing this directive]---------- FOLLOW-UP ----------

QUESTION: Thank you Tom that works perfectly for me.

Can you please explain the returned values of the array formula
so I can understand better how to construct the nested formauls in future?



if column A has the value in R2, then the If formula returns the row number. If not it returns false.  

so you would end up with an array of row numbers

{3,4,false, false,5,...}

when we use small with this, it returns the smallest numerical value when the 2nd argument is 1.  we produce the 1 by using  row(A1) and small returns (3)  then when we drag the formula down, it becomes row(A2) and returns a 2 - so the small formula returns the 2nd smallest value (4) and so forth.   This give us the rows where column A conatins the value in R2.  

so we use these row numbers to index into column B and return the values you want.

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Thanks Tom, that's really helpful.

Why the extra error trapping for version XL 2007 and what does making it an array formula do compared to just the nest functions by themselves?



If you   say small({1,2,3,false, false},4)

you get an error because their is not a 4th smallest number.  Since you don't know how many valid data points you will need (how many row of formulas), this makes it a little more robust if you will be changing the data.  If it is a one time deal, then you can just pull down until you run out of data (produce an error).

Why an array formula - it won't work unless it is an array formula.  Making it an array formula causes it to treat its arguments as arrays and that is required in this case for it to work.

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 About.com. All rights reserved.

[an error occurred while processing this directive]