SOLVED

Index and match

%3CLINGO-SUB%20id%3D%22lingo-sub-1659869%22%20slang%3D%22en-US%22%3EIndex%20and%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1659869%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Ei%20am%20trying%20to%20write%20an%20index%20formula%20for%20the%20following%20two%20tables.%20I%20want%20to%20write%20an%20array%20formula%20that%20returns%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3E26%3C%2FTD%3E%3CTD%3E39%3C%2FTD%3E%3CTD%3E52%3C%2FTD%3E%3CTD%3E65%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3Bby%20matching%20311.585%20number%20from%20the%20following%20two%20table%20sets%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22384%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E311.535%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E8%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E53%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3CTD%3E36%3C%2FTD%3E%3CTD%3E48%3C%2FTD%3E%3CTD%3E60%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E15%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E311.585%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E12%3C%2FTD%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3E26%3C%2FTD%3E%3CTD%3E39%3C%2FTD%3E%3CTD%3E52%3C%2FTD%3E%3CTD%3E65%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E1008%3C%2FTD%3E%3CTD%3E19%3C%2FTD%3E%3CTD%3E38%3C%2FTD%3E%3CTD%3E57%3C%2FTD%3E%3CTD%3E76%3C%2FTD%3E%3CTD%3E95%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20my%20problem%20is%20that%20i%20can't%20tell%20the%20formula%20to%20look%20up%20311.585%20and%20return%20the%20table%20number%20as%202%20when%20typing%20the%20area_num%20for%20the%20array%20function.%20Also%20when%20i%20type%20the%20formula%20it%20starts%20returning%20values%20as%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E12%3C%2FTD%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3E26%3C%2FTD%3E%3CTD%3E39%3C%2FTD%3E%3CTD%3E52%3C%2FTD%3E%3CTD%3E65%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3Binstead%20of%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3E26%3C%2FTD%3E%3CTD%3E39%3C%2FTD%3E%3CTD%3E52%3C%2FTD%3E%3CTD%3E65%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1659869%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1660313%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1660313%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F656583%22%20target%3D%22_blank%22%3E%40jiwanjot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20clarify%20why%20returned%20numbers%20shall%20start%20from12%2C%20not%2013%20and%20how%20it%20is%20connected%20to%20the%20position%26nbsp%3B%20of%20311.535%20or%20311.585%20(they%20are%20different%20in%20your%20sample).%20As%20variant%20that%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20406px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F217963iFBF90E429258688E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ebut%20that's%20another%20logic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1660755%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1660755%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehi%20i%20want%20a%20formula%20that%20looks%20up%20these%20two%20tables%20based%20on%20my%20lookup%20value%20of%20311.535%20or%20311.585%20and%20returns%20an%20array%20that%20starts%20from%20second%20column%20and%20second%20row%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1660943%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1660943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F656583%22%20target%3D%22_blank%22%3E%40jiwanjot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20lookup%20value%20could%20be%20in%20any%20position%20or%20always%20in%20the%204th%20column%20from%20the%20range%20start%3F%20Other%20words%2C%20how%20do%20we%20know%20where%20is%20the%20first%20column%20of%20the%20range.%3C%2FP%3E%0A%3CP%3ESecond%20row%20as%20I%20understood%20is%20the%20next%20to%20the%20one%20in%20which%20the%20value%20found.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20question%20is%20what%20is%20the%20logic%20of%20how%20to%20find%20end%20of%20the%20range%20-%20is%20it%20of%20predefined%20size%20or%20it%20shall%20be%20found%20based%20on%20some%20logic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1660964%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1660964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20lookup%20value%20will%20always%20be%20in%204th%20column%3C%2FP%3E%3CP%3Esecond%20answer%20is%20that%20it%20there%20is%20a%20predefined%20size%20to%20the%20array%3C%2FP%3E%3CP%3EPlease%20see%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

i am trying to write an index formula for the following two tables. I want to write an array formula that returns

1326395265

 by matching 311.585 number from the following two table sets

501311.5350 
81020304050
531224364860
      
1501311.5850 
121326395265
10081938577695

 

Basically my problem is that i can't tell the formula to look up 311.585 and return the table number as 2 when typing the area_num for the array function. Also when i type the formula it starts returning values as

121326395265

 instead of

1326395265

 

Please help

 

Thanks

7 Replies

@jiwanjot 

Could you please clarify why returned numbers shall start from12, not 13 and how it is connected to the position  of 311.535 or 311.585 (they are different in your sample). As variant that could be

image.png

but that's another logic.

@Sergei Baklan 

hi i want a formula that looks up these two tables based on my lookup value of 311.535 or 311.585 and returns an array that starts from second column and second row

@jiwanjot 

and lookup value could be in any position or always in the 4th column from the range start? Other words, how do we know where is the first column of the range.

Second row as I understood is the next to the one in which the value found.

 

Another question is what is the logic of how to find end of the range - is it of predefined size or it shall be found based on some logic.

@Sergei Baklan 

The lookup value will always be in 4th column

second answer is that it there is a predefined size to the array

Please see attached

Best Response confirmed by cuong (Microsoft)
Solution

@jiwanjot 

Better to have sample file instead of screenshot, but for such model

image.png

formula could be

=OFFSET(INDEX(F:F,MATCH($K5,F:F,0)),COUNTIF($K$5:$K5,$K5),-2,1,6)

@Sergei Baklan 

This works exactly how i wanted. Thank you so much.

@jiwanjot , you are welcome