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

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies