Vertical lookup when the range changes.

  • I have a large range B8:ED10. I'm finding my first value using HLOOKUP (this formula works fine). Once I have found the correct value using HLOOKUP, which could occur anywhere in the range I need to return the value one column to the right of the HLOOKUP cell. I've tried mucking with INDEX and MATCH but everything seems to require a fixed range. I need the range to be defined by the cell found using HLOOKUP. It shouldn't be that difficult but I can't find anything searching the net. Thanks in advance

  • If you use index Match, you can add +1 to the Match portion of the formula to adjust to next column...


    e.g. =INDEX(B8:ED10,MATCH(A1,B8:B10,0),MATCH(A2,E8:ED10,0)+1)

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • I'm sorry I'm not very good with the index, match so your reply doesn't make sense to me, why would the MATCH value change from A1 to A2. Or perhaps I wasn't very clear in what I need to do. Using HLOOKUP (this formula works) to find the first value which could occur in cell B8, B9, B10, G8, G9, G10 etc... right up to EB8, EB9, EB10 I would then need to return the value located in C8, C9, C10, H8, H9, H10 etc... all the way to EC8, EC9, EC10.

  • Here it is.


    =HLOOKUP(I5,'Annual Totals'!A5:EB10,(3+Cheque!G5),FALSE)


    And it works nested in VLOOKUP, but then of course I can only search one column in the range.


    =VLOOKUP(HLOOKUP(I5,'Annual Totals'!A5:EB10,(3+Cheque!G5),FALSE),'Annual Totals'!B8:ED10,2,FALSE)


    The value that the HLOOKUP returns is in the B8:ED10 Range.

  • Does this work?


    =INDEX('Annual Totals'!B8:ED10,SUMPRODUCT((ISNUMBER(SEARCH(HLOOKUP(I5,'Annual Totals'!A5:EB10,(3+Cheque!G5),FALSE),'Annual Totals'!B8:ED10)))*(ROW('Annual Totals'!B8:ED10)-7)),SUMPRODUCT((ISNUMBER(SEARCH(HLOOKUP(I5,'Annual Totals'!A5:EB10,(3+Cheque!G5),FALSE),'Annual Totals'!B8:ED10)))*(COLUMN('Annual Totals'!B8:ED10)-1))+1)

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016