Merging 2 VLOOKUPS where the lookup value has multiple answers

%3CLINGO-SUB%20id%3D%22lingo-sub-2142060%22%20slang%3D%22en-US%22%3EMerging%202%20VLOOKUPS%20where%20the%20lookup%20value%20has%20multiple%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2142060%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20working%20in%20Tables%20and%20when%20I%20enter%20a%20formula%20it%20carries%20down%20to%20all%20records%2C%20however%20I%20would%20like%20to%20enter%20a%20different%20formula%20for%202%20sets%20of%20data.%3C%2FP%3E%3CP%3EThe%20data%20I%20wish%20to%20lookup%20relates%20to%20States%20of%20Australia%20-%20I%20need%20to%20have%20one%20rule%20for%20NSW%20and%20another%20for%20all%20other%20States%20(classified%20as%20%22Interstate%22)%2C%20accessing%20information%20from%202%20separate%20worksheets.%20I%20have%20tried%20an%20IF%20function%20but%20can't%20quite%20work%20out%20how%20to%20make%20the%202%20VLOOKUPS%20separate.%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3ENSW%20-%20information%20relates%20to%20NSW%20only%20and%20is%20different%20to%20all%20other%20States%2C%20with%20info%20being%20extracted%20from%20a%20NSW%20worksheet%2C%3C%2FP%3E%3CP%3EInterstate%20-%20information%20has%20been%20collated%20so%20that%20it%20is%20consistent%20for%20all%20other%20States%2C%20but%20has%20a%20different%20outcome%20to%20the%20NSW%20ones%2C%20with%20info%20extracted%20from%20an%20Interstate%20spreadsheet.%3C%2FP%3E%3CP%3ECan%20you%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2142060%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hi, I am working in Tables and when I enter a formula it carries down to all records, however I would like to enter a different formula for 2 sets of data.

The data I wish to lookup relates to States of Australia - I need to have one rule for NSW and another for all other States (classified as "Interstate"), accessing information from 2 separate worksheets. I have tried an IF function but can't quite work out how to make the 2 VLOOKUPS separate.

For example:

NSW - information relates to NSW only and is different to all other States, with info being extracted from a NSW worksheet,

Interstate - information has been collated so that it is consistent for all other States, but has a different outcome to the NSW ones, with info extracted from an Interstate spreadsheet.

Can you help?

1 Reply

@Sue_H1160 Try something like:

=IF([@State]="NSW",<VLOOKUP function NSW>,<VLOOKUP function for Interstate>)

assuming you work with a structured table where the column for the state is called "State".