VLOOKUP multiple search locations

%3CLINGO-SUB%20id%3D%22lingo-sub-1651474%22%20slang%3D%22en-US%22%3EVLOOKUP%20multiple%20search%20locations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1651474%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20get%20excel%20to%20VLOOKUP%20multiple%20columns%20and%20return%20a%20result%20from%201%20column%20but%20cant%20work%20it%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20detail%3A%3C%2FP%3E%3CP%3ESheet%201%20holds%20an%20overview%20of%20info%20for%20a%20device%2C%20(about%202000%20rows)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esheet%202%20holds%20a%20mass%20of%20other%20details%20regarding%20power%20source%20for%20devices.%20(About%201700%20rows)%3C%2FP%3E%3CP%3Ewithin%20sheet%202%20there%20is%20only%20one%20column%20of%20results%20needed%2C%20(shown%20in%20green)%20and%208%20potential%20locations%20the%20ID%20number%20from%20Sheet%20one%20might%20be%20placed.%3CBR%20%2F%3E%3CBR%20%2F%3Ecurrently%20i%20am%20using%20VLOOKUP%20and%20IFERROR%20formulas%20but%20there%20has%20to%20be%20an%20easier%20way%3F%3C%2FP%3E%3CP%3EVLOOKUP%20could%20be%20the%20way%20forward%20if%20it%20can%20Look%20up%20multiple%20columns%20rather%20then%20just%20the%201st%20in%20the%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20anyone%20help%20me%20with%20a%20better%20solution%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1651474%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1651691%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20multiple%20search%20locations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1651691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F788244%22%20target%3D%22_blank%22%3E%40PatarmaganM%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESay%20the%20columns%20you%20want%20to%20check%20are%20in%20A2%3AO100%20and%20the%20data%20you%20want%20to%20return%20is%20in%20column%20T.%26nbsp%3B%20You%20could%20try%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(T%3AT%2CMIN(IF(A2%3AO100%3D%22Device%22%2CROW(A2%3AO100)%2C%22%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEntered%20with%20Ctrl%2BShift%2BEnter%20after%20keying%2Fcopying%20into%20the%20formula%20bar.%20Also%2C%20replace%20%22Device%22%20with%20whatever%20your%20device%20name%20is%20you're%20trying%20to%20find%20or%20a%20cell%20reference%20to%20the%20device%20name.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I'm trying to get excel to VLOOKUP multiple columns and return a result from 1 column but cant work it out.

 

in detail:

Sheet 1 holds an overview of info for a device, (about 2000 rows)

 

sheet 2 holds a mass of other details regarding power source for devices. (About 1700 rows)

within sheet 2 there is only one column of results needed, (shown in green) and 8 potential locations the ID number from Sheet one might be placed.

currently i am using VLOOKUP and IFERROR formulas but there has to be an easier way?

VLOOKUP could be the way forward if it can Look up multiple columns rather then just the 1st in the array.

 

can anyone help me with a better solution?

 

1 Reply

@PatarmaganM 

 

Say the columns you want to check are in A2:O100 and the data you want to return is in column T.  You could try:

 

=INDEX(T:T,MIN(IF(A2:O100="Device",ROW(A2:O100),"")))

 

Entered with Ctrl+Shift+Enter after keying/copying into the formula bar. Also, replace "Device" with whatever your device name is you're trying to find or a cell reference to the device name.