Home

double indexed lookup

%3CLINGO-SUB%20id%3D%22lingo-sub-390519%22%20slang%3D%22en-US%22%3Edouble%20indexed%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390519%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20attached.%26nbsp%3B%20The%20named%20range%20is%20a%20small%20version%20of%20what%20I%20actually%20use%2C%20but%20illustrates%20the%20point.%26nbsp%3B%20I'll%20have%20many%20of%20these%2C%20and%20there%20are%20many%20more%20rows%20pertaining%20to%20a%20particular%20named%20range.%26nbsp%3B%20Right%20now%2C%20for%20me%20to%20find%20the%20vertical%20spot%20column%20L%20needs%20to%20look%20at%2C%20I'm%20pointing%20(absolute%20reference)%20to%20a%20number%2C%20which%20I've%20generated%20with%20a%20simple%20row%20subtraction.%26nbsp%3B%20What%20I'd%20really%20like%20is%20for%20the%20row%20number%20part%20of%20the%20hlookup%2C%20the%20hlookup%20has%20a%20function%20within%20it%20that%20basically%20says%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22look%20in%20column%20K...whatever%20is%20there%2C%20go%20find%20it%20in%20the%20range%2C%20and%20us%20that%20line%20as%20the%20line%20to%20return%20(for%20the%20appropriate%20hlooked%20up%20value%20across%20the%20top%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20thinking%20it's%20some%20sort%20of%20index%20or%20find%2C%20or%20combination.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EImportant%20because%20the%20number%20of%20items%20and%20the%20number%20of%20hard%20points%20I'm%20having%20to%20do%20is%20getting%20tedious.%26nbsp%3B%20I%20need%20one%20named%20range%20(which%20is%20my%20assumptions%20area%20I'm%20asking%20someone%20to%20fill%20in%2C%20including%20additional%20potential%20items)%2C%20and%20then%20the%20formulas%20do%20the%20rest%20of%20the%20work...meaning%20I%20can%20take%20a%20section%20of%20pivot%20list%20lines%2C%20and%20bulk%20copy%20them%20(basically%20what%20I%20did%20for%20the%20'That%20item'%20lookup%2C%20simply%20changing%20the%20absolute%20pointer%20manually.%26nbsp%3B%20When%20I%20have%20to%20create%20an%20additional%2025%20of%20them%2C%20it's%20more%20troubling%20to%20redo%20the%20absolute%20point.%26nbsp%3B%20And%20reality%20is%20that%20there%20will%20be%20about%20100%20individually%20tracked%20assumptions%2C%20across%20various%20named%20ranges...so%20having%20a%20standard%20formula%20that%20does%20the%20legwork%20of%20looking%20horizontally%20AND%20vertically%2C%20to%20find%20the%20right%20spot%2C%20is%20key%20for%20efficiency.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-390519%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-407134%22%20slang%3D%22en-US%22%3ERe%3A%20double%20indexed%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-407134%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299524%22%20target%3D%22_blank%22%3E%40txrussianguy%3C%2FA%3E%26nbsp%3BI%20honestly%20did%20not%20get%20what%20you%20need%2C%20but%20let%20me%20ask%3A%20have%20you%20tried%20with%20INDEX%20and%20MATCH%20functions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
txrussianguy
Contributor

I have the attached.  The named range is a small version of what I actually use, but illustrates the point.  I'll have many of these, and there are many more rows pertaining to a particular named range.  Right now, for me to find the vertical spot column L needs to look at, I'm pointing (absolute reference) to a number, which I've generated with a simple row subtraction.  What I'd really like is for the row number part of the hlookup, the hlookup has a function within it that basically says:

 

"look in column K...whatever is there, go find it in the range, and us that line as the line to return (for the appropriate hlooked up value across the top"

 

I'm thinking it's some sort of index or find, or combination. 

 

Important because the number of items and the number of hard points I'm having to do is getting tedious.  I need one named range (which is my assumptions area I'm asking someone to fill in, including additional potential items), and then the formulas do the rest of the work...meaning I can take a section of pivot list lines, and bulk copy them (basically what I did for the 'That item' lookup, simply changing the absolute pointer manually.  When I have to create an additional 25 of them, it's more troubling to redo the absolute point.  And reality is that there will be about 100 individually tracked assumptions, across various named ranges...so having a standard formula that does the legwork of looking horizontally AND vertically, to find the right spot, is key for efficiency.

 

Thanks for any help.

2 Replies
Highlighted

@txrussianguy I honestly did not get what you need, but let me ask: have you tried with INDEX and MATCH functions?

Highlighted
Related Conversations
Hidden Names are Displaying
KCMFCC in SharePoint on
2 Replies
Document Lookup Column Link
Phil_COB in SharePoint on
8 Replies
Getting changes to Lookups to trigger a crawl
melkinsco in Microsoft Search on
2 Replies
Copy cell value from different table row
Edgar Soares in Excel on
2 Replies