Home |
Search |
Today's Posts |
#1
|
|||
|
|||
simultaneous v&h lookup - approaches...
AFAIK, Excel has no function allowing simultaneous search for 1 value
along leftmost column of a table range and then also search horizontally across table top row. 1 way to do this is to embed a LOOKUP to figure out how many columns or rows to index over (in case of VLOOKUP/HLOOKUP respectively). Anything else? |
#2
|
|||
|
|||
One way is with
INDEX(WholeDataRange,MATCH(xyz,Column_Data,0),MATC H(xyz,Row_Data,0)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- wrote in message ups.com... AFAIK, Excel has no function allowing simultaneous search for 1 value along leftmost column of a table range and then also search horizontally across table top row. 1 way to do this is to embed a LOOKUP to figure out how many columns or rows to index over (in case of VLOOKUP/HLOOKUP respectively). Anything else? |
#3
|
|||
|
|||
tripp
Don't know if this would help....... If you have your rows and columns named you can use the intersection operation to find a value. Or if you have labels, say across row 1 and down column A you can use them to create an intersection table. i.e. A2:E5 have data. B1:E1 have title1, title2, title3, title4 A2:A5 have item1, item2, item3, item4 Select A1:E5 InsertNameCreate. Check top row and left column. Now in H4 enter =title2 item3 Works great for mileage charts and similar operations but might not be applicable for your needs. Gord Dibben Excel MVP On 11 Feb 2005 13:31:19 -0800, wrote: AFAIK, Excel has no function allowing simultaneous search for 1 value along leftmost column of a table range and then also search horizontally across table top row. 1 way to do this is to embed a LOOKUP to figure out how many columns or rows to index over (in case of VLOOKUP/HLOOKUP respectively). Anything else? |
#4
|
|||
|
|||
Index + Match is probably the fastest:
See Ken's example Array Sum is probably the shortest: =SUM((A6=A2:A4)*(A7=B1:F1)*(B2:F4)) Confirm the formula with Ctrl+Shift and Enter. Unless you use the intersection approach: See http://www.dicks-blog.com/archives/2...and-intersect/ or Gordon's example. Ola Sandstrom |
#5
|
|||
|
|||
That second xyz should really have been something else, as you likely
wouldn't use the same variable in both places. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Ken Wright" wrote in message ... One way is with INDEX(WholeDataRange,MATCH(xyz,Column_Data,0),MATC H(xyz,Row_Data,0)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- wrote in message ups.com... AFAIK, Excel has no function allowing simultaneous search for 1 value along leftmost column of a table range and then also search horizontally across table top row. 1 way to do this is to embed a LOOKUP to figure out how many columns or rows to index over (in case of VLOOKUP/HLOOKUP respectively). Anything else? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a lookup table with an added varable? | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions | |||
Index table lookup anomaly | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |