INDEX Forumula

[Ruzzo]

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

 

[Aladin Akyurek]

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),"")

 

[Ruzzo]

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

 

[Ruzzo]

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.

 

[Ruzzo]

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.

 

[Aladin Akyurek]

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),"")

 

[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).

Thanks in advance.
Ruzzo

 

[Aladin Akyurek]

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

=IFERROR(INDEX('Sheet 1'!$C$1:$C$4,
  MATCH(A1,'Sheet 1'!$A$1:$A$4,0)),0)

Or...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
  INDEX('Sheet 1'!$C$1:$C$4,MATCH(A1,'Sheet 1'!$A$1:$A$4,0))))

 

[Ruzzo]

Thank you. The first one works perfectly.

 

[Aladin Akyurek]

Thank you. The first one works perfectly.

You are welcome.