Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

compare text strings and add an existing value

0

I hope I can explain it well: I have one big table which has the following data: 

Amstelveenseweg 1040   Bijlmerdreef 677 11,3 Amstelveenseweg 1040   Charlotte Brontestraat 46A 11,7 Amstelveenseweg 1040   Elysee 46,9 Bijlmerdreef 184C   Stationsstraat 18A 12,5 Bijlmerdreef 677   Elysee 53,5 Bijlmerdreef 794   Frederik Hendrikstraat 30 17,1

1e column is the first street 2nd column is the destination street and the 3rd column is the distance between column 1 and 2.

Now my problem and question: In my daily work I have to keep up were I drive and the distance: 

This sheet looks likes this: 

Geleenstraat   Hogevecht 188B 9,1 Hogevecht 188B   Amstelveenseweg 1040 12,5 Amstelveenseweg 1040   Elysee 47 Elysee   Geleenstraat 48     Totaal 116,6

What I want is that if I fill in the First two horizontal streets (Geleenstraat and Hogevecht 188B Excel fill in the distance from the data from the existing tabel.

I have tried Vlookup,Index, match but nothing works. I hope someone can help me I hope it is clearly defined Englishis not my mother language, Thanks for someone help looking forward for the solution

excel
MS Office Version: Excel2010
posted by: silona (rep: 4) on Tue Jul 30, 2019 at 2:01 pm
Answer
Discuss

Answers

0
Selected Answer

Have you tried using a wildcard? Your data set seems to have multiple different values for the same lookup value, but I'm not sure, and this would cause an issue, but try a wildcard vlookup and see how it works.

Wildcards in Excel allow you to make partial matches of cell contents.

You basically just put asterisks around the lookup value like this:

=VLOOKUP("*"&C1&"*",A1:B3,2,FALSE)
posted by: don (rep: 1989) on Tue Jul 30, 2019 at 10:01 pm
Discuss

Discussion

Don thank you but either your formule doesn't work. I try to explain it again.
I create a text string from to streets without a distance. In my lookup table consists this streets with a distance. What I want is, when I select the new text strings, the vlook surch in the consistend table to the same text string and find the distance.
I tried this formue: =VLOOKUP("*" &A4&"*";Look!A1:D53;4;1) but get as reply #N/A
silona (rep: 4) Jul 31, '19 at 6:19 am
Don's answer "*"&C1&"*" produces a text string, meaning you are looking for a string. If A1:A3 are numbers a match won't be found. You might store the number as text, perhaps. I suggest you attach a workbook to your question. Also consider asking a new, better documented question, since you already selected an answer for this one.
Variatus (rep: 4889) Jul 31, '19 at 8:31 pm
Even with your current workbook, I'm not sure where you tried to apply that, on which sheet, so it's a bit difficult to help you troubleshoot. Provide a bit more clarity on where/how you want to use it in the sample file and I can look.
don (rep: 1989) Aug 1, '19 at 11:11 am
Add to Discussion
0

Please find attached herewith a copy of your workbook in which I have made the following changes.

  1. I have created dynamic named ranges for your table of distances as follows.
    Range name "From" =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$D:$D))
Range name "GoTo" =OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$D:$D))
Range Name "Distance" =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D))

    Observe that the size of all ranges, although they start at different first cells, is determined by the number of entries in column D. This is intentionally so because the ranges must have identical sizes.
    I used COUNTA for all columns so as to make the formula almost identical.
  2. A proper NumberFormat was given to all columns containing km, such as Sheet1!D:D and Sheet2!D:D. The number format = 0.0 but, actually, it is a built-in number format which you can select from Home > Format Cells > Number > Number.
  3. I entered the formula you asked about in D5 and copied down to D6:D10 and D16:D19. This formula uses the named ranges explained above. Remember, they are dynamic. New distances you enter will be included automatically. (I added one fictitious distance at the bottom of your list.)
    =SUMIFS(Distance,From,$A5,GoTo,$C5)

    This formula returns zero if the From and GoTo points aren't listed (or if the listed distance is 0 km).
    As an afterthought, I prefer the formula below (not included in the attached workbook) which is a variation of the one above.
    =MAX(SUMIFS(Distance,From,$A5,GoTo,$C5),SUMIFS(Distance,From,$C5,GoTo,$A5))

    This formula will look up the distance from A to B and from B to A and return the larger one. Since both distances are the same the question actually being asked is whether both are listed. The one not listed will return zero and the other one will be the larger number. This will save you the trouble of listing distances in both directions.
  4. I entered a dynamic formula in D11.
    =SUM(OFFSET($D$5,0,0,ROW()-ROW($D$5)))

    As you enter more distances above it they will automatically be included in the total.
  5. I copied this formula to D20, then made 2 changes to it. Observe that D16 is the first cell in your list of distances. It occurs twice in the formula.
    =SUM(OFFSET($D$16,0,0,ROW()-ROW($D$16)))

    It would probably be possible to create a formula that can be copied across the sheet without changes but that is too much work to be applied to a hypothetical worksheet where you intend to still continue development. Ask about it when you are all done and settled in your design.
posted by: Variatus (rep: 4889) on Thu Aug 1, 2019 at 9:17 pm
Discuss

Discussion

Don,

I am gratefull for your help I couldn't make it by myself. Thank you very much.
Only one question left, Were did you create the dynamic named ranges for the table of distances . I cann't find it.
Again thank you very much for helping.
Simon van Straten
Amsterdam
silona (rep: 4) Aug 2, '19 at 7:40 am
Hello Simon, Named ranges are managed by the Names Manager which you will find on the Ribbon's Formulas tab. Click on a name in the manager's dialog box to see the formula that defines the range in the RefersTo box at the bottom. You can create a new named range by clicking on the New button in the Names Manger's dialog box. That will open another dialog box whre you can enter the RefersTo range as well as a name of your choice. You will want the scope to be Workbook, meaning that the name can be accessed from any worksheet.
BTW, I thought that your project could be greatly improved if you would create validation dropdowns (Data > Validation > List) in A5 and C5 using the lists from named ranges From and GoTo respectively. Copy down to A5:A10 and C5:C10, A16:A19 and C16:C19. In this way you would avoid entering addresses that don't exist, for example, by misspelling. The Validation drop-down would show whatever is in the named range. You would need to exclude duplicates. There was a recent thead here where I published a formula for that.
Variatus (rep: 4889) Aug 2, '19 at 11:03 am
I want to thank Variatus, I have als o created the dropdowns, all works now perfect.
Again thanks for your help.
Simon van Straten
Amsterdam, The Netherlands
silona (rep: 4) Aug 3, '19 at 5:28 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login