HELP!! VLOOKUP, INDEX or MATCH??
 

I am trying to figure out how to get a piece of information into a cell
needed for a calculation.

This is what I have in one worksheet:

8 10 12 15 18 21
2.0 0.138 0.155 0.132 0.121 0.105 0.083
2.2 0.156 0.175 0.152 0.143 0.130 0.111
2.4 0.174 0.194 0.173 0.167 0.155 0.138

The table keeps going...

B1:G1 are pipe sizes and A2:A4 are trench depths and in B2:G4 is CY/LF. In a
different worksheet I have a cell that has the pipe size and a cell that has
a depth of trench. What i would like to happen is when the pipe size and
depth of trench cells become populated on my other worksheet, i would like a
formula in my CY/LF cell to take the pipe size and trench depth and display
the value at the interesection of those two cells. (8 inch pipe with a depth
of 2.2 should return me the number 0.156)

Any help would be greatly appreciated!

Kurt

HELP!! VLOOKUP, INDEX or MATCH??
 

Try this...

Assuming your lookup table is in the range A1:G4

A20 = 8
B20 = 2.2

=VLOOKUP(B20,$A$1:$G$4,MATCH(A20,$A$1:$G$1,0),0)

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
I am trying to figure out how to get a piece of information into a cell
needed for a calculation.

This is what I have in one worksheet:

8 10 12 15 18 21
2.0 0.138 0.155 0.132 0.121 0.105 0.083
2.2 0.156 0.175 0.152 0.143 0.130 0.111
2.4 0.174 0.194 0.173 0.167 0.155 0.138

The table keeps going...

B1:G1 are pipe sizes and A2:A4 are trench depths and in B2:G4 is CY/LF. In
a
different worksheet I have a cell that has the pipe size and a cell that
has
a depth of trench. What i would like to happen is when the pipe size and
depth of trench cells become populated on my other worksheet, i would like
a
formula in my CY/LF cell to take the pipe size and trench depth and
display
the value at the interesection of those two cells. (8 inch pipe with a
depth
of 2.2 should return me the number 0.156)

Any help would be greatly appreciated!

Kurt

HELP!! VLOOKUP, INDEX or MATCH??
 

Here is what I put in:
=VLOOKUP(T12,'TBF TABLE'!$C$2:$Z$81,MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0),0)
with T12 being my depth cell in my calc tab (worksheet) and E12 is my pipe
size in my calc tab (worksheet).
and here is what i get when with that formula: #N/A

"T. Valko" wrote:

Try this...

Assuming your lookup table is in the range A1:G4

A20 = 8
B20 = 2.2

=VLOOKUP(B20,$A$1:$G$4,MATCH(A20,$A$1:$G$1,0),0)

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
I am trying to figure out how to get a piece of information into a cell
needed for a calculation.

This is what I have in one worksheet:

8 10 12 15 18 21
2.0 0.138 0.155 0.132 0.121 0.105 0.083
2.2 0.156 0.175 0.152 0.143 0.130 0.111
2.4 0.174 0.194 0.173 0.167 0.155 0.138

The table keeps going...

B1:G1 are pipe sizes and A2:A4 are trench depths and in B2:G4 is CY/LF. In
a
different worksheet I have a cell that has the pipe size and a cell that
has
a depth of trench. What i would like to happen is when the pipe size and
depth of trench cells become populated on my other worksheet, i would like
a
formula in my CY/LF cell to take the pipe size and trench depth and
display
the value at the interesection of those two cells. (8 inch pipe with a
depth
of 2.2 should return me the number 0.156)

Any help would be greatly appreciated!

Kurt

HELP!! VLOOKUP, INDEX or MATCH??
 

VLOOKUP(T12 is the vertical lookup
MATCH(E12 is the horizontal lookup

MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0)

The horizontal lookup array *must* be a single row. Try:

MATCH(E12,'TBF TABLE'!$C$2:$Z$2,0)

Here's an example:

...........A..........B..........C..........D
1....................x............y..........z
2........5..........3...........8..........6
3........7..........4...........1..........2

To lookup 5 and y:

A10 = 5
B10 = y

=VLOOKUP(A10,A1:D3,MATCH(B10,A1:D1,0),0)

Result = 8

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
Here is what I put in:
=VLOOKUP(T12,'TBF TABLE'!$C$2:$Z$81,MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0),0)
with T12 being my depth cell in my calc tab (worksheet) and E12 is my pipe
size in my calc tab (worksheet).
and here is what i get when with that formula: #N/A

"T. Valko" wrote:

Try this...

Assuming your lookup table is in the range A1:G4

A20 = 8
B20 = 2.2

=VLOOKUP(B20,$A$1:$G$4,MATCH(A20,$A$1:$G$1,0),0)

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
I am trying to figure out how to get a piece of information into a cell
needed for a calculation.

This is what I have in one worksheet:

8 10 12 15 18 21
2.0 0.138 0.155 0.132 0.121 0.105 0.083
2.2 0.156 0.175 0.152 0.143 0.130 0.111
2.4 0.174 0.194 0.173 0.167 0.155 0.138

The table keeps going...

B1:G1 are pipe sizes and A2:A4 are trench depths and in B2:G4 is CY/LF.
In
a
different worksheet I have a cell that has the pipe size and a cell
that
has
a depth of trench. What i would like to happen is when the pipe size
and
depth of trench cells become populated on my other worksheet, i would
like
a
formula in my CY/LF cell to take the pipe size and trench depth and
display
the value at the interesection of those two cells. (8 inch pipe with a
depth
of 2.2 should return me the number 0.156)

Any help would be greatly appreciated!

Kurt

HELP!! VLOOKUP, INDEX or MATCH??
 

That seems to work as long as i dont put in the ,0),0) at the end of the
formula.
Thanks for the help!

"T. Valko" wrote:

VLOOKUP(T12 is the vertical lookup
MATCH(E12 is the horizontal lookup

MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0)

The horizontal lookup array *must* be a single row. Try:

MATCH(E12,'TBF TABLE'!$C$2:$Z$2,0)

Here's an example:

...........A..........B..........C..........D
1....................x............y..........z
2........5..........3...........8..........6
3........7..........4...........1..........2

To lookup 5 and y:

A10 = 5
B10 = y

=VLOOKUP(A10,A1:D3,MATCH(B10,A1:D1,0),0)

Result = 8

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
Here is what I put in:
=VLOOKUP(T12,'TBF TABLE'!$C$2:$Z$81,MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0),0)
with T12 being my depth cell in my calc tab (worksheet) and E12 is my pipe
size in my calc tab (worksheet).
and here is what i get when with that formula: #N/A

"T. Valko" wrote:

Try this...

Assuming your lookup table is in the range A1:G4

A20 = 8
B20 = 2.2

=VLOOKUP(B20,$A$1:$G$4,MATCH(A20,$A$1:$G$1,0),0)

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
I am trying to figure out how to get a piece of information into a cell
needed for a calculation.

This is what I have in one worksheet:

8 10 12 15 18 21
2.0 0.138 0.155 0.132 0.121 0.105 0.083
2.2 0.156 0.175 0.152 0.143 0.130 0.111
2.4 0.174 0.194 0.173 0.167 0.155 0.138

The table keeps going...

B1:G1 are pipe sizes and A2:A4 are trench depths and in B2:G4 is CY/LF.
In
a
different worksheet I have a cell that has the pipe size and a cell
that
has
a depth of trench. What i would like to happen is when the pipe size
and
depth of trench cells become populated on my other worksheet, i would
like
a
formula in my CY/LF cell to take the pipe size and trench depth and
display
the value at the interesection of those two cells. (8 inch pipe with a
depth
of 2.2 should return me the number 0.156)

Any help would be greatly appreciated!

Kurt

HELP!! VLOOKUP, INDEX or MATCH??
 

Then you should check your results carefully.

If this does not work:
MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0)

but this "works":
MATCH(E12,'TBF TABLE'!$C$2:$Z$81)

then you are probably not getting the correct result from MATCH. Please
read the help section on MATCH, in particular how the "match_type"
parameter works.

Kurt wrote:
That seems to work as long as i dont put in the ,0),0) at the end of the
formula.
Thanks for the help!

"T. Valko" wrote:

VLOOKUP(T12 is the vertical lookup
MATCH(E12 is the horizontal lookup

MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0)

The horizontal lookup array *must* be a single row. Try:

MATCH(E12,'TBF TABLE'!$C$2:$Z$2,0)

Here's an example:

...........A..........B..........C..........D
1....................x............y..........z
2........5..........3...........8..........6
3........7..........4...........1..........2

To lookup 5 and y:

A10 = 5
B10 = y

=VLOOKUP(A10,A1:D3,MATCH(B10,A1:D1,0),0)

Result = 8

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
Here is what I put in:
=VLOOKUP(T12,'TBF TABLE'!$C$2:$Z$81,MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0),0)
with T12 being my depth cell in my calc tab (worksheet) and E12 is my pipe
size in my calc tab (worksheet).
and here is what i get when with that formula: #N/A

"T. Valko" wrote:

Try this...

Assuming your lookup table is in the range A1:G4

A20 = 8
B20 = 2.2

=VLOOKUP(B20,$A$1:$G$4,MATCH(A20,$A$1:$G$1,0),0)

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
I am trying to figure out how to get a piece of information into a cell
needed for a calculation.

This is what I have in one worksheet:

8 10 12 15 18 21
2.0 0.138 0.155 0.132 0.121 0.105 0.083
2.2 0.156 0.175 0.152 0.143 0.130 0.111
2.4 0.174 0.194 0.173 0.167 0.155 0.138

The table keeps going...

B1:G1 are pipe sizes and A2:A4 are trench depths and in B2:G4 is CY/LF.
In
a
different worksheet I have a cell that has the pipe size and a cell
that
has
a depth of trench. What i would like to happen is when the pipe size
and
depth of trench cells become populated on my other worksheet, i would
like
a
formula in my CY/LF cell to take the pipe size and trench depth and
display
the value at the interesection of those two cells. (8 inch pipe with a
depth
of 2.2 should return me the number 0.156)

Any help would be greatly appreciated!

Kurt

HELP!! VLOOKUP, INDEX or MATCH??
 

but this "works":
MATCH(E12,'TBF TABLE'!$C$2:$Z$81)

That will *never* work. The lookup_array has to be a one dimensional array =
a reference to a single row or a reference to a single column or a one
dimensional array of calculated values.

Apparently the OP is looking for a "closest match" if an exact match isn't
present.


--
Biff
Microsoft Excel MVP


"smartin" wrote in message
...
Then you should check your results carefully.

If this does not work:
MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0)

but this "works":
MATCH(E12,'TBF TABLE'!$C$2:$Z$81)

then you are probably not getting the correct result from MATCH. Please
read the help section on MATCH, in particular how the "match_type"
parameter works.

Kurt wrote:
That seems to work as long as i dont put in the ,0),0) at the end of the
formula.
Thanks for the help!

"T. Valko" wrote:

VLOOKUP(T12 is the vertical lookup
MATCH(E12 is the horizontal lookup

MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0)

The horizontal lookup array *must* be a single row. Try:

MATCH(E12,'TBF TABLE'!$C$2:$Z$2,0)

Here's an example:

...........A..........B..........C..........D
1....................x............y..........z
2........5..........3...........8..........6
3........7..........4...........1..........2

To lookup 5 and y:

A10 = 5
B10 = y

=VLOOKUP(A10,A1:D3,MATCH(B10,A1:D1,0),0)

Result = 8

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
Here is what I put in:
=VLOOKUP(T12,'TBF TABLE'!$C$2:$Z$81,MATCH(E12,'TBF
TABLE'!$C$2:$Z$81,0),0)
with T12 being my depth cell in my calc tab (worksheet) and E12 is my
pipe
size in my calc tab (worksheet).
and here is what i get when with that formula: #N/A

"T. Valko" wrote:

Try this...

Assuming your lookup table is in the range A1:G4

A20 = 8
B20 = 2.2

=VLOOKUP(B20,$A$1:$G$4,MATCH(A20,$A$1:$G$1,0),0)

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
I am trying to figure out how to get a piece of information into a
cell
needed for a calculation.

This is what I have in one worksheet:

8 10 12 15 18
21
2.0 0.138 0.155 0.132 0.121 0.105 0.083
2.2 0.156 0.175 0.152 0.143 0.130 0.111
2.4 0.174 0.194 0.173 0.167 0.155 0.138

The table keeps going...

B1:G1 are pipe sizes and A2:A4 are trench depths and in B2:G4 is
CY/LF. In
a
different worksheet I have a cell that has the pipe size and a cell
that
has
a depth of trench. What i would like to happen is when the pipe size
and
depth of trench cells become populated on my other worksheet, i would
like
a
formula in my CY/LF cell to take the pipe size and trench depth and
display
the value at the interesection of those two cells. (8 inch pipe with
a
depth
of 2.2 should return me the number 0.156)

Any help would be greatly appreciated!

Kurt

HELP!! VLOOKUP, INDEX or MATCH??
 

I got the formula to work like it should. I think the problem was that my
vertical column numbers were 2.0, 2.2, 2.4 etc and i was getting numbers like
2.1, 2.15. I am going to fix it by adding 2.1, 2.3, 2.4 and then have my cell
roundup to the nearest .1. Does that seem like that should take care of my
problem? I thank you guys for all of the help!!

Kurt

"T. Valko" wrote:

but this "works":
MATCH(E12,'TBF TABLE'!$C$2:$Z$81)

That will *never* work. The lookup_array has to be a one dimensional array =
a reference to a single row or a reference to a single column or a one
dimensional array of calculated values.

Apparently the OP is looking for a "closest match" if an exact match isn't
present.


--
Biff
Microsoft Excel MVP


"smartin" wrote in message
...
Then you should check your results carefully.

If this does not work:
MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0)

but this "works":
MATCH(E12,'TBF TABLE'!$C$2:$Z$81)

then you are probably not getting the correct result from MATCH. Please
read the help section on MATCH, in particular how the "match_type"
parameter works.

Kurt wrote:
That seems to work as long as i dont put in the ,0),0) at the end of the
formula.
Thanks for the help!

"T. Valko" wrote:

VLOOKUP(T12 is the vertical lookup
MATCH(E12 is the horizontal lookup

MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0)

The horizontal lookup array *must* be a single row. Try:

MATCH(E12,'TBF TABLE'!$C$2:$Z$2,0)

Here's an example:

...........A..........B..........C..........D
1....................x............y..........z
2........5..........3...........8..........6
3........7..........4...........1..........2

To lookup 5 and y:

A10 = 5
B10 = y

=VLOOKUP(A10,A1:D3,MATCH(B10,A1:D1,0),0)

Result = 8

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
Here is what I put in:
=VLOOKUP(T12,'TBF TABLE'!$C$2:$Z$81,MATCH(E12,'TBF
TABLE'!$C$2:$Z$81,0),0)
with T12 being my depth cell in my calc tab (worksheet) and E12 is my
pipe
size in my calc tab (worksheet).
and here is what i get when with that formula: #N/A

"T. Valko" wrote:

Try this...

Assuming your lookup table is in the range A1:G4

A20 = 8
B20 = 2.2

=VLOOKUP(B20,$A$1:$G$4,MATCH(A20,$A$1:$G$1,0),0)

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
I am trying to figure out how to get a piece of information into a
cell
needed for a calculation.

This is what I have in one worksheet:

8 10 12 15 18
21
2.0 0.138 0.155 0.132 0.121 0.105 0.083
2.2 0.156 0.175 0.152 0.143 0.130 0.111
2.4 0.174 0.194 0.173 0.167 0.155 0.138

The table keeps going...

B1:G1 are pipe sizes and A2:A4 are trench depths and in B2:G4 is
CY/LF. In
a
different worksheet I have a cell that has the pipe size and a cell
that
has
a depth of trench. What i would like to happen is when the pipe size
and
depth of trench cells become populated on my other worksheet, i would
like
a
formula in my CY/LF cell to take the pipe size and trench depth and
display
the value at the interesection of those two cells. (8 inch pipe with
a
depth
of 2.2 should return me the number 0.156)

Any help would be greatly appreciated!

Kurt

HELP!! VLOOKUP, INDEX or MATCH??
 

It depends on what your requirements are.

The VLOOKUP/MATCH functions can find exact matches or "close matches".

You tell the function which type of match you want by including the
range_lookup argument in VLOOKUP or the match_type argument in the MATCH
fucntion.

If you only want exact matches then the data can be in random order. If you
want a "close match" when there isn't an exact match then the data *must* be
sorted in a specific order. See Excel help on the VLOOKUP and MATCH
functions.

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
I got the formula to work like it should. I think the problem was that my
vertical column numbers were 2.0, 2.2, 2.4 etc and i was getting numbers
like
2.1, 2.15. I am going to fix it by adding 2.1, 2.3, 2.4 and then have my
cell
roundup to the nearest .1. Does that seem like that should take care of
my
problem? I thank you guys for all of the help!!

Kurt

"T. Valko" wrote:

but this "works":
MATCH(E12,'TBF TABLE'!$C$2:$Z$81)

That will *never* work. The lookup_array has to be a one dimensional
array =
a reference to a single row or a reference to a single column or a one
dimensional array of calculated values.

Apparently the OP is looking for a "closest match" if an exact match
isn't
present.


--
Biff
Microsoft Excel MVP


"smartin" wrote in message
...
Then you should check your results carefully.

If this does not work:
MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0)

but this "works":
MATCH(E12,'TBF TABLE'!$C$2:$Z$81)

then you are probably not getting the correct result from MATCH. Please
read the help section on MATCH, in particular how the "match_type"
parameter works.

Kurt wrote:
That seems to work as long as i dont put in the ,0),0) at the end of
the
formula.
Thanks for the help!

"T. Valko" wrote:

VLOOKUP(T12 is the vertical lookup
MATCH(E12 is the horizontal lookup

MATCH(E12,'TBF TABLE'!$C$2:$Z$81,0)

The horizontal lookup array *must* be a single row. Try:

MATCH(E12,'TBF TABLE'!$C$2:$Z$2,0)

Here's an example:

...........A..........B..........C..........D
1....................x............y..........z
2........5..........3...........8..........6
3........7..........4...........1..........2

To lookup 5 and y:

A10 = 5
B10 = y

=VLOOKUP(A10,A1:D3,MATCH(B10,A1:D1,0),0)

Result = 8

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
Here is what I put in:
=VLOOKUP(T12,'TBF TABLE'!$C$2:$Z$81,MATCH(E12,'TBF
TABLE'!$C$2:$Z$81,0),0)
with T12 being my depth cell in my calc tab (worksheet) and E12 is
my
pipe
size in my calc tab (worksheet).
and here is what i get when with that formula: #N/A

"T. Valko" wrote:

Try this...

Assuming your lookup table is in the range A1:G4

A20 = 8
B20 = 2.2

=VLOOKUP(B20,$A$1:$G$4,MATCH(A20,$A$1:$G$1,0),0)

--
Biff
Microsoft Excel MVP


"Kurt" wrote in message
...
I am trying to figure out how to get a piece of information into a
cell
needed for a calculation.

This is what I have in one worksheet:

8 10 12 15 18
21
2.0 0.138 0.155 0.132 0.121 0.105 0.083
2.2 0.156 0.175 0.152 0.143 0.130 0.111
2.4 0.174 0.194 0.173 0.167 0.155 0.138

The table keeps going...

B1:G1 are pipe sizes and A2:A4 are trench depths and in B2:G4 is
CY/LF. In
a
different worksheet I have a cell that has the pipe size and a
cell
that
has
a depth of trench. What i would like to happen is when the pipe
size
and
depth of trench cells become populated on my other worksheet, i
would
like
a
formula in my CY/LF cell to take the pipe size and trench depth
and
display
the value at the interesection of those two cells. (8 inch pipe
with
a
depth
of 2.2 should return me the number 0.156)

Any help would be greatly appreciated!

Kurt