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!

    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.

    What's the current HLookup formula?

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

    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.

    So the result of the nested HLOOKUP could be in any cell withing B8:ED10 or is it in a specific column only?

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

    The result of the HLOOKUP could occur in column B and every 5th column thereafter, G, L, P etc. up to column EB. Then the value I need to retrieve would be one column to the right.

    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!

    Yes it does!! I'm not sure exactly how what you just wrote works but thank you, thank you, thank you. My brain has been going in circles on this since late Friday morning.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login