1. Re: Vlookup (2003)

Do you have to use VLookup?
Is it not better to use a nested Sub Total from the Data Menu to get the totals!

2. Vlookup (2003)

(Edited by HansV to display data in table)

How can I use the vlookup function to look for a part of a word.
Example :

<table border=1><td>Column A</td><td>Column B</td><td>Canada</td><td align=right>25</td><td>Canada - Toronto</td><td align=right>18</td><td>Canada - Montreal</td><td align=right>17</td><td>UK - London</td><td align=right>23</td></table>
What vlookup formula can I use to return the totals for Canada only but to also include the two city breakouts ?

3. Re: Vlookup (2003)

No I don't have to use vlookup - anyone any ideas ?

4. Re: Vlookup (2003)

The attached workbook contains two possibilities. In the first worksheet, country and city have been placed in separate columns, and subtotals by country have been added. In the second workbook, the location has been left "as is", but a column has been added to extract the country. Subtotals on this calculated column have been added.

5. Re: Vlookup (2003)

Hi,

I would make it a two step operation:

1. Use the LEFT function in another cell (C1) to separate the country from the city as such:
=IF(ISERROR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1))
If you paste this into C1 it will provide the country name (one word, or no spaces) separate from the city. It looks for the first space and then extracts the characters to the left.

2. You can then use SUMIF to get your totals:

Good Luck!

Peter Moran

6. Re: Vlookup (2003)

What about the array formula: =SUM((LEFT(A1:A5,LEN(E1))=E1)*B1:B5) where E1 contains what you're looking for (e.g., "Canada" or "UK - London")

7. Re: Vlookup (2003)

"To return the totals for Canada only but to also include the two city breakouts"