ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   How to get a value for a different cell using one cell on the same row? (https://www.excelbanter.com/excel-worksheet-functions/131159-how-get-value-different-cell-using-one-cell-same-row.html)

Zilla[_2_]

How to get a value for a different cell using one cell on the same row?
 
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???

--
- Zilla
(Remove XSPAM)



JLatham

How to get a value for a different cell using one cell on the same
 
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.

"Zilla" wrote:

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

--
- Zilla
(Remove XSPAM)




Roger Govier

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

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

--
Regards

Roger Govier


"Zilla" wrote in message
...
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???

--
- Zilla
(Remove XSPAM)





Zilla[_2_]

How to get a value for a different cell using one cell on the same
 
Exactly what I was looking for, TY!!!

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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.

"Zilla" wrote:

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

--
- Zilla
(Remove XSPAM)







All times are GMT +1. The time now is 05:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com