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

Z

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???
 
J

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

Zilla

Exactly what I was looking for, TY!!!

JLatham said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top