# INDEX Forumula

#### Ruzzo

##### Board Regular
Sheet 1 has the following:
A B C D
1 Trucks (20.00) 10.00 10.00
2 Cars (400.00) 20.00 20.00

Sheet 2 has Trucks and Cars listed again as such- how do I INDEX Sheet 2, to input the same info in cells C1 & D1…and C2 & D2, as it has in Sheet 1.

In other words, Sheet 2 may have different values in the ‘B’ column. And the order of Trucks, Cars, and other vehicles may be not in the same order.

Hope this makes sense.

Ruzzo

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sheet 1 has the following:
A B C D
1 Trucks (20.00) 10.00 10.00
2 Cars (400.00) 20.00 20.00

Sheet 2 has Trucks and Cars listed again as such- how do I INDEX Sheet 2, to input the same info in cells C1 & D1…and C2 & D2, as it has in Sheet 1.

In other words, Sheet 2 may have different values in the ‘B’ column. And the order of Trucks, Cars, and other vehicles may be not in the same order.

Hope this makes sense.

Ruzzo

Sheet2

B2:

=MATCH(A2,Sheet1!\$A\$2:\$A\$4,0)

C2, copy across:

=IF(ISNUMBER(\$B2),INDEX(Sheet1!B\$2:B\$4,\$B2),"")

Just to illustrate further, as my formatting didn't come out right in my post:
A1 = Trucks
A2 = Cars
C1 = 10.00
D1 = 10.00
C2 = 20.00
D2 = 20.00

Sheet2

B2:

=MATCH(A2,Sheet1!\$A\$2:\$A\$4,0)

C2, copy across:

=IF(ISNUMBER(\$B2),INDEX(Sheet1!B\$2:B\$4,\$B2),"")

Thanks..will give it a try.

Sheet2

B2:

=MATCH(A2,Sheet1!\$A\$2:\$A\$4,0)

C2, copy across:

=IF(ISNUMBER(\$B2),INDEX(Sheet1!B\$2:B\$4,\$B2),"")

Not sure if this is what I need, as it didn't work.
Sheet 2 will have values already populated in the B column. I don't want to replace those values, with a forumula. I want to keep those values.
I just want to take what is in columns C and D, from Sheet 1, and put into Sheet 2, if there is a match in the A column.

Thanks again.

Not sure if this is what I need, as it didn't work.
Sheet 2 will have values already populated in the B column. I don't want to replace those values, with a forumula. I want to keep those values.
I just want to take what is in columns C and D, from Sheet 1, and put into Sheet 2, if there is a match in the A column.

Thanks again.
w

You didn't say where in Sheet2 you wanted the result values. It now appears I guessed wrong for I assumed colum B to be free.
That said:

Sheet2

L2, copy down:

=MATCH(A2,Sheet1!\$A\$2:\$A\$4,0)

J2, copy across to K2, and copy down:

=IF(ISNUMBER(\$L2),INDEX(Sheet1!C\$2:C\$4,\$B2),"")

Thanks, I actually found a formula that worked better from another file.
=(INDEX('Sheet 1'!\$C\$1:\$C\$4,MATCH(A1,'Sheet 1'!\$A\$1:\$A\$4,FALSE),1))

The problem now is that some values come back fine, but some come back as an error (#N/A).
Let’s say my current worksheet does not have an entry for Trucks this time around…that’s why it comes back as an error.
How do I set it up so errors either come back as blank, or as zero (preferably as zero).

Ruzzo

Thanks, I actually found a formula that worked better from another file.
=(INDEX('Sheet 1'!\$C\$1:\$C\$4,MATCH(A1,'Sheet 1'!\$A\$1:\$A\$4,FALSE),1))

The problem now is that some values come back fine, but some come back as an error (#N/A).
Let’s say my current worksheet does not have an entry for Trucks this time around…that’s why it comes back as an error.
How do I set it up so errors either come back as blank, or as zero (preferably as zero).

Ruzzo

It's in fact the same formula I proposed... My proposal is actually cheaper and the error values will only show up in L2 where MATCH is.

Using a bit less efficient version you have, try:

Either...
Rich (BB code):
``````=IFERROR(INDEX('Sheet 1'!\$C\$1:\$C\$4,
MATCH(A1,'Sheet 1'!\$A\$1:\$A\$4,0)),0)``````
Or...
Rich (BB code):
``````=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
INDEX('Sheet 1'!\$C\$1:\$C\$4,MATCH(A1,'Sheet 1'!\$A\$1:\$A\$4,0))))``````

Thank you. The first one works perfectly.

Replies
2
Views
415
Replies
3
Views
688
Replies
2
Views
466
Replies
3
Views
223
Replies
1
Views
177

1,211,686
Messages
6,103,297
Members
447,853
Latest member
olddutch7

### 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