Which function have I to choose?

[world33]

Hello,

I have got three columns A, B, C.

In column A I have 7 fax numbers. In column B I have 7 URLs where faxes have been extracted from. In column C I have 14 URLs in which, among others, are included the exact URLs in column B.

I want associate faxes in column A with URLs (matched with those in column B) present in column C.

Ex.

COL A

1 1-785-227-2004

2 208-376-7743

3 (617) 912-9101

4 267-502-2658

5 (317) 940-9930

6 716-888-2525

7 (219) 473-4259

COL B

1 www.bethanylb.edu/

2 http://www.boisebible.edu/

3 www.conservatory.edu/

4 www.brynathyn.edu/

5 www.butler.edu/

6 http://www.canisius.edu/

7 http://www.ccsj.edu/

COL C--------------------------------------- COL D

1 www.ab.edu/

2 www.abc.edu/

3 www.abconline.edu/

4 www.ac.edu/

5 www.aca.edu/

6 www.academy.edu/

7 http://www.boisebible.edu/-----------> 208-376-7743

8 www.boisestate.edu/

9 www.brevard.edu/

10 http://www.canisius.edu/-------------> 716-888-2525

11 www.carleton.edu/

12 www.carroll.edu/

13 http://www.ccsj.edu/------------------> (219) 473-4259

14 www.clemson.edu/

Which function should I use in order to get faxes in Col A and put them next to the appropriate URLs (matched with URLs in column B) in column C ?

Thanks a lot !!

Fabio

[Atim Arap Ben Arap]

@world33

I solved your problem with a small excel macro.

There is a file at the bottom of this message which included an example like what you wanted.

In order to open this, you must enable macros.

When you want to modificate it; while working on file, press Alt+F11 (it opens VBA window) and go to Module1.

Enjoy...

CompareURLs.rar

[jkfegan]

The VLOOKUP function is the best answer.

***FIRST***You'll have to reorder your columns (Swap the data in columns A and B) but then you can get your desired result without the need for a macro.

I've attached an Excel file to demostrate how VLOOKUP works with your data. You can see more details in the help files.

Notice, that I had to create a named range for a table array. I set a range called RANGE1 equal to A1:B8 (which is the current list of URLs/Telephone numbers). RANGE1 should change to fit your input data size. For more details on naming a range, go to Insert | Name | Define.

=VLOOKUP(C2,RANGE1,2,0)

In the example above, VLOOKUP will search the first column in RANGE1 (ie COL A) for the search parameter (the data in C2). When it finds a match it will return the value of the data in COL B that corresponds with the row of the match in COL A. This is a *really* great tool for managing data in large spreadsheets. For instance, if you had two telephone numbers (or addresses, or names, IPs, etc) you could set up a bunch of VLOOKUPs to pull out all of that data automatically. Your functions might look something like:

=VLOOKUP(F2,RANGE2,2,0)

=VLOOKUP(G2,RANGE2,3,0)

=VLOOKUP(H2,RANGE2,4,0)

=VLOOKUP(I2,RANGE2,5,0)

RANGE2 would have to define a larger range (like A1:E8).

I did a poor job of explaining it. The best way to find out more is to play around with it and look it up in the help files.

I hope this helps...

-J

Answer_using_VLOOKUP.zip