INDEX Forumula

Ruzzo

Board Regular
Joined
Nov 22, 2004
Messages
83
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.

Thanks in advance
Ruzzo
 

Excel Facts

Shade all formula cells
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.

Thanks in advance
Ruzzo

Sheet2

B2:

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

C2, copy across:

=IF(ISNUMBER($B2),INDEX(Sheet1!B$2:B$4,$B2),"")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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),"")
 
Upvote 0
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).

Thanks in advance.
Ruzzo
 
Upvote 0
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).

Thanks in advance.
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))))
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top