Use a cell value for the row number in Vlookup.

%3CLINGO-SUB%20id%3D%22lingo-sub-2877075%22%20slang%3D%22en-US%22%3EUse%20a%20cell%20value%20for%20the%20row%20number%20in%20Vlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2877075%22%20slang%3D%22en-US%22%3E%3CP%3EOn%20sheet%201%20in%20row%205%20and%20column%20h%20for%20example%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3Bthe%20formula%2C%26nbsp%3B%3DIFERROR(-VLOOKUP('Sheet%203'!E13%2CSheet2!A%3AK%2C6%2CFALSE)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20I%20would%20like%20to%20replace%20the%20number%20in%20E13%2C%20with%20a%20value%20derived%20from%20cell%20D%20of%20the%20same%20row%20as%20the%20formula%2C%20so%20the%20E13%20becomes%20E%20%22value%20of%20cell%20D5%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeally%20I%20can%20then%20drag%20the%20formula%20into%20other%20rows%20so%20that%20the%20value%20of%20the%20row%20used%20in%20the%20look%20up%20changes%20according%20to%20the%20value%20in%20column%20D%20of%20the%20row%20where%20the%20formula%20sits.%3C%2FP%3E%3CP%3EIf%20anyone%20knows%20of%20a%20tidy%20way%20of%20making%20that%20happen%20it%20would%20save%20me%20a%20lot%20of%20typing.%3C%2FP%3E%3CP%3EUnfortunately%20the%20values%20in%20column%20D%20are%20not%20sequential.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2877075%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2877120%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20a%20cell%20value%20for%20the%20row%20number%20in%20Vlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2877120%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1193928%22%20target%3D%22_blank%22%3E%40Flow2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20this%20do%20what%20you%20want%3F%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIFERROR(-VLOOKUP(INDIRECT(%22'Sheet%203'!E%22%26amp%3BD5)%2CSheet2!A%3AK%2C6%2CFALSE)%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2877523%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20a%20cell%20value%20for%20the%20row%20number%20in%20Vlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2877523%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1193928%22%20target%3D%22_blank%22%3E%40Flow2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20small%20sample%20workbook%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2877654%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20a%20cell%20value%20for%20the%20row%20number%20in%20Vlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2877654%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20your%20help%20with%20this%2C%20it%20has%20given%20me%20the%20clue%20for%20the%20answer%3B%3CBR%20%2F%3E%3DIFERROR(-VLOOKUP(INDIRECT(%22'SHEET%203'!I%22%26amp%3BD5)%2C'SHEET%202'!A%3AK%2C6%2CFALSE)%2C0)%3CBR%20%2F%3EThe%20only%20difference%20to%20your%20is%20the%20'%20around%20SHEET%202%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20will%20save%20me%20hours%20of%20typing%2C%20as%20well%20as%20the%20risk%20of%20typos.%3CBR%20%2F%3EI%20hope%20the%20formula%20here%20will%20help%20others%20too.%3C%2FLINGO-BODY%3E
Occasional Contributor

On sheet 1 in row 5 and column h for example 

 the formula, =IFERROR(-VLOOKUP('Sheet 3'!E13,Sheet2!A:K,6,FALSE),0)

 

However I would like to replace the number in E13, with a value derived from cell D of the same row as the formula, so the E13 becomes E "value of cell D5"

 

Ideally I can then drag the formula into other rows so that the value of the row used in the look up changes according to the value in column D of the row where the formula sits.

If anyone knows of a tidy way of making that happen it would save me a lot of typing.

Unfortunately the values in column D are not sequential.

9 Replies

@Flow2 

Does this do what you want?

=IFERROR(-VLOOKUP(INDIRECT("'Sheet 3'!E"&D5),Sheet2!A:K,6,FALSE),0)
Hi Hans,

Thank you for posting a potential answer, unfortunately that does not work, although I can see your logic.

@Flow2 

Could you attach a small sample workbook?

Hi Hans,

Thank you for your help with this, it has given me the clue for the answer;
=IFERROR(-VLOOKUP(INDIRECT("'SHEET 3'!I"&D5),'SHEET 2'!A:K,6,FALSE),0)
The only difference to your is the ' around SHEET 2

This will save me hours of typing, as well as the risk of typos.
I hope the formula here will help others too.

@Flow2 

Your first post had Sheet2 instead of Sheet 2...

Hi Hans,

The key difference is the punctuation around the name of the tab.
e.g 'Tab Name'

@Flow2 

I know that, but never mind...

Hans, you couldn't have known because Flow2's original post didn't use ' around Sheet2 but did use it around 'Sheet 3' so it would appear his Sheet2 name didn't have a space ... but then later it did.