HELP!! VLOOKUP, INDEX or MATCH??

K

Kurt

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
 
T

T. Valko

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)
 
K

Kurt

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

T. Valko

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
 
K

Kurt

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

smartin

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

T. Valko

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

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

Kurt

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

T. Valko

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