Formula Help

[decfish]

Hi there,

Bit of a complex one here ..I think !
But here's an example of what i have anyway

A B C
D E F
93 Afghanistan Afghanistan 17.22 17.22 17.22
9370 Afghanistan-Mobile Afghanistan-Mobile 16.40 16.40 16.40
9371 Afghanistan-Mobile Albania 6.04 6.04 6.04
9372 Afghanistan-Mobile Albania-Mobile 11.30 11.30 11.30
9379 Afghanistan-Mobile Albania-Tirana 3.47 3.47
3.47


Basically what i need it to do , is check column B against column C and
if it has an entry in column C then to copy the values in D,E and F and
paste them into empty columns on the same row.
So, in effect what we would be left with is

93 Afghanistan Afghanistan 17.22 17.22 17.22
9370 Afghanistan-Mobile Afghanistan-Mobile 16.4 16.4 16.4
9371 Afghanistan-Mobile Afghanistan-Mobile 16.4 16.4 16.4
9372 Afghanistan-Mobile Afghanistan-Mobile 16.4 16.4 16.4
9379 Afghanistan-Mobile Afghanistan-Mobile 16.4 16.4 16.4

Is this even possible to do as i'm having a lot of issues trying to
implement it correctly myself ?
Also to note is that there is a large number of entries in column B and
a smaller number in column C.
It doesnt really matter what output column C is either, as all that
matters is to have the correct number of lines with the right output in
columns G,H and I or where ever.

Many thanks in advance.
Declan

 

[Pete_UK]

Hi Declan,

I think you have two tables - the first (columns A and B) comprises the
phone code and the destination and the second (columns C to F)
comprises the destination and the rates charged. You want to end up
with one table incorporating all the data.

I think the easiest way is to insert three new columns, C D and E.
Highlight the second table, for example from f1 to i500 (or whatever
your range is) and Insert | Name | Define - give this range a name such
as "Prices". Then you can enter these formulae in columns C D and E:

C1: =VLOOKUP(B1,Prices,2,0)
D1: =VLOOKUP(B1,Prices,3,0)
E1: =VLOOKUP(B1,Prices,4,0)

Copy the formulae down for as many entries as you have in column B by
double-clicking the fill handle (the small black square at the bottom
right corner of the cursor, with C1 selected).

You can fix the values in columns C to E by highlighting them, click
<copy> then Edit | Paste Special | Values (check) | OK then <Enter>,
and then you can delete columns f to i to leave yourself with a single
composite table. I'm not sure why you need the destination column
repeated.

Hope this helps.

Pete

 

[decfish]

Perfect stuff thanks Pete,
Seems i was going about it totally the wrong way ..and you made it soo
simple
Always the case !
Thanks a million though.

Very much appreciated

Dec

 

[Pete_UK]

Thanks for the feedback, Declan -you must have tried the solution out
very promptly.

Pete

 

[decfish]

Yes, got into work at 9 and saw your help posted..and because it got
the better of me yesterday (trying nested if formulas and all sorts)
seeing your solution made it all slot nicely into place

thanks once again,
Dec