# conditional formula

#### mr audit

##### New Member
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?

(btw, I'm using Excel 2003)

Last edited:

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Gerald Higgins

##### Well-known Member
VLOOKUP will do this (there may be other, better ways).

In C4
Code:
``=if(iserror(vlookup(a4,e\$1:f\$3,1,false)),"",+a4)``

In D4
Code:
``=if(c4="","",vlookup(a4,e\$1:f\$3,2,false)``

#### jim may

##### Well-known Member
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

##### MrExcel MVP, Moderator
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}

Replies
0
Views
145
Replies
0
Views
156
Replies
6
Views
267
Replies
19
Views
736
Replies
7
Views
136

1,195,590
Messages
6,010,612
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back