conditional formula

[mr audit]

Hello all,

I'm having some difficulties figuring something out. Here's an example:

__A______________B_________C__________D__________E_____________F
1 Company 1______$1______________________________Company 1_____$5
2 Company 2______$3______________________________Company 4_____$6
3 Company 3______$9______________________________Company 6_____$7
4 Company 4______$6
5 Company 5______$2
6 Company 6______$4
7 Company 7______$8


What I'd like to do is have the data in E & F be placed into columns C & D respetively if the company names match. So for example, in box C4 it should say Company 4 and in box D4 it should say $6.

What formula should I enter into the C & D columns in order to make this happen for any matching company names?

Thanks in advance.

(btw, I'm using Excel 2003)

 

[Gerald Higgins]

VLOOKUP will do this (there may be other, better ways).

In C4

=if(iserror(vlookup(a4,e$1:f$3,1,false)),"",+a4)

In D4

=if(c4="","",vlookup(a4,e$1:f$3,2,false)

 

[jim may]

Kudos to Gerald, but I did it (as follows):

Cell C1 and copy down:

=IF(ISNA(VLOOKUP(A1,$E$1:$F$3,1,0)),"",VLOOKUP(A1,$E$1:$F$3,1,0))

Cell D1 and copy down:

=IF(ISNA(VLOOKUP(A1,$E$1:$F$3,2,0)),"",VLOOKUP(A1,$E$1:$F$3,2,0))

 

[Peter_SSs]

The advantage of Gerald's is that each formula only has to evaluate the VLOOKUP once.

I would consider the use of ISNA as Jim did though, and use:
C1: =IF(ISNA(MATCH(A1,E$1:E$3,0)),"",A1)
D1: =IF(C1="","",VLOOKUP(A1,E$1:F$3,2,0)) {Same as Gerald}