How to get a value for a different cell using one cell on the same row?

[Zilla]

I hope that makes sense. Here's what I want to do.

I have Sheet1 and Sheet2.

Sheet1 has...
A B C
1 Route# Route Mileage
-------------------------------------
2 1 A-B 10
3 2 B-C 20
........
16 15 Y-Z 30

Sheet2 has...
A B
1 Truck# Route#/Mileage
------------------------
2 1 1
3 Formula?
4 2 4
5 Formula?

I want to reference Sheet1 in Sheet2 so
if I enter a Route# in Sheet2's cell, it'll
associated this number with the Route#
in Sheet1, and get the corresponding
Mileage from Sheet1.
So the Sheet2 cells (with Formula? above)
I want to do this on will have this pseudo-formula,
just to illustrate what I want.

=if(Sheet2!C1=Sheet1!A2,Sheet1!C2,0) OR
if(Sheet2!C4=Sheet1!A3,Sheet1!C3,0) OR
............
if(Sheet2!C14=Sheet1!A2=15,Sheet1!C6,0)

Make sense???

 

[JLatham]

If I'm reading this right and have understood you, I think what you need on
sheet 2 is a VLOOKUP()
here's what it would look like in Sheet2!B3 (where you have the first
Formula? entry shown)
=VLOOKUP(B2,Sheet1!$A$2:$C$16,3,0)
That should return the mileage from column C on sheet 1.
B2 is the 'what to lookup',
Sheet1!A2:C16 is a table to search in, with possible matches to value in B2
listed in the first column of that table.
,3, tells it to return value from 3rd column of the table if a match is
found, and
,0) says that the items in the search column (A) don't necessarily have to
be in order.

Hope this helps some.

 

[Roger Govier]

Hi

Try
=IF(COUNTIF(Sheet1!B:B,B2),VLOOKUP(B2,Sheet1!B:C,2,0),"")

 

[Zilla]

Exactly what I was looking for, TY!!!

If I'm reading this right and have understood you, I think what you need on
sheet 2 is a VLOOKUP()
here's what it would look like in Sheet2!B3 (where you have the first
Formula? entry shown)
=VLOOKUP(B2,Sheet1!$A$2:$C$16,3,0)
That should return the mileage from column C on sheet 1.
B2 is the 'what to lookup',
Sheet1!A2:C16 is a table to search in, with possible matches to value in B2
listed in the first column of that table.
,3, tells it to return value from 3rd column of the table if a match is
found, and
,0) says that the items in the search column (A) don't necessarily have to
be in order.

Hope this helps some.