Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup, hlookup and return value of intersecting cell
I want to lookup a value of intersecting cells, find rate for specific trip.
The rates are now summarized in a table by looking up first column and top row the intersecting cells give the rate. how do I do this. Column A (Departure) Column B (Destination) Column C (Rate) When column C is complete i will select all cells to arrange it according to rate of trip. Thanks W |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup, hlookup and return value of intersecting cell
Hi,
This assumes a table in A1 - H13 and the 2 lookup values in J1 & K1. =INDEX(A1:H13,MATCH(J1,A1:A13,0),MATCH(K1,A1:H1,0) ) Mike "willemeulen" wrote: I want to lookup a value of intersecting cells, find rate for specific trip. The rates are now summarized in a table by looking up first column and top row the intersecting cells give the rate. how do I do this. Column A (Departure) Column B (Destination) Column C (Rate) When column C is complete i will select all cells to arrange it according to rate of trip. Thanks W |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup, hlookup and return value of intersecting cell
My formula looks as follows:
=INDEX(sheet1!$A$2:$Q$18,MATCH(A2,sheet1!$A$2:$A$1 8,0),MATCH(B2,sheet1!$A$2:$Q$2,0)) for some reason the value returned is not what it should be. the lookup table is on sheet 1 and I am working on sheet 2. The lookup/index values are letters like CHI etc and return number a value. should the lookup/index value be ascending? Or What else could be the reason for returning wrong value? "Mike H" wrote: Hi, This assumes a table in A1 - H13 and the 2 lookup values in J1 & K1. =INDEX(A1:H13,MATCH(J1,A1:A13,0),MATCH(K1,A1:H1,0) ) Mike "willemeulen" wrote: I want to lookup a value of intersecting cells, find rate for specific trip. The rates are now summarized in a table by looking up first column and top row the intersecting cells give the rate. how do I do this. Column A (Departure) Column B (Destination) Column C (Rate) When column C is complete i will select all cells to arrange it according to rate of trip. Thanks W |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup, hlookup and return value of intersecting cell
found the mistake, got it solved.
thanks mike "willemeulen" wrote: My formula looks as follows: =INDEX(sheet1!$A$2:$Q$18,MATCH(A2,sheet1!$A$2:$A$1 8,0),MATCH(B2,sheet1!$A$2:$Q$2,0)) for some reason the value returned is not what it should be. the lookup table is on sheet 1 and I am working on sheet 2. The lookup/index values are letters like CHI etc and return number a value. should the lookup/index value be ascending? Or What else could be the reason for returning wrong value? "Mike H" wrote: Hi, This assumes a table in A1 - H13 and the 2 lookup values in J1 & K1. =INDEX(A1:H13,MATCH(J1,A1:A13,0),MATCH(K1,A1:H1,0) ) Mike "willemeulen" wrote: I want to lookup a value of intersecting cells, find rate for specific trip. The rates are now summarized in a table by looking up first column and top row the intersecting cells give the rate. how do I do this. Column A (Departure) Column B (Destination) Column C (Rate) When column C is complete i will select all cells to arrange it according to rate of trip. Thanks W |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP/HLOOKUP to Return Several Results | Excel Worksheet Functions | |||
Can you use HLOOKUP inside VLOOKUP to tell it what column 2 return | Excel Worksheet Functions | |||
Can you link HLOOKUP AND VLOOKUP formulas and return their interce | Excel Worksheet Functions | |||
Alternative to Vlookup/Hlookup to return a value. | Excel Worksheet Functions | |||
RETURN intersecting value with known horizotal & vertical?? | Excel Worksheet Functions |