for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

icenutter Nov 2019 9:56am, 27 Nov 2019 302 posts	Quote Pin icenutter index: match copes a lot better if the data isn't in sequential order too.
larkim Nov 2019 10:05am, 27 Nov 2019 9,597 posts	Quote Pin larkim Can someone write out the pseudo code for index match? In my head when I do a lookup I'm saying:- =Vlookup("Find this", "in the first column of this data", "and give me the value from this column", "and then I always type 'false’ because it doesn't work otherwise") Could you do the same for index match?
GlennR Nov 2019 10:12am, 27 Nov 2019 46,555 posts	Quote Pin GlennR I'll give you some real code. Hang on a mo.
um Nov 2019 10:23am, 27 Nov 2019 1,421 posts	Quote Pin um While Glenn is searching, I found this quite easy to understand and has examples exceluser.com Thanks all for the reasons you prefer index/match - was interesting to see. Hadn't thought of or considered the left look as well.
GlennR Nov 2019 10:31am, 27 Nov 2019 46,556 posts	Quote Pin GlennR Here you go: =INDEX($S$3:$W$282,MATCH($Z8,$A$3:$A$282,0),MATCH(AA$7,$S$2:$W$2,0)) $S$3:$W$282 is the range that contains the lookup data, in this case cost code tabulated against year. Column Z contains the cost code I want to look up, Row AA the year. $A$3:$A$282 is the array of cost codes. $S$2:$W$2 is the array of years. The formula looks at the data range and returns the figure at the row of the first MATCH and the column of the second. The zero at the end of the MATCH formulae makes the function look for an exact match.
Pothunter Nov 2019 10:37am, 27 Nov 2019 1,735 posts	Quote Pin Pothunter I found MrExcel.com a very clear guide. Www.mrexcel.com/excel-tips/excel-vlookup-index-match/
SPR Nov 2019 10:37am, 27 Nov 2019 29,708 posts	Quote Pin SPR If you're using INDEX and MATCH like VLOOKUP INDEX (Column containing result, MATCH(Find this, In this Column, 0)) 0 = Exact Can also be typed as INDEX (Column containing result, MATCH(Find this, In this Column, 0), 1) The 1 at the end is unnecessary though if only one column is selected for the results bit.
SPR Nov 2019 10:39am, 27 Nov 2019 29,709 posts	Quote Pin SPR Glenn's example above of doing a H and V at the same time is actually how I came across INDEX in the first place.
larkim Nov 2019 10:42am, 27 Nov 2019 9,600 posts	Quote Pin larkim Ive not seen an index( match(), match()) formulation before. It'd still be nice to have the pseudo code done in my language And I'm consued with "Row AA" as I thought all rows were numerical? AA would be a column, no? Or am I wilfuly misunderstanding?
larkim Nov 2019 10:43am, 27 Nov 2019 9,601 posts	Quote Pin larkim Cross posted with SPR. That formulation I can live with!