SOLVED

Functions and Formulas, which is best

%3CLINGO-SUB%20id%3D%22lingo-sub-2797648%22%20slang%3D%22en-US%22%3EFunctions%20and%20Formulas%2C%20which%20is%20best%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2797648%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20function%20and%20formula%20do%20I%20use%20to%20generate%20the%20desired%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tikitour_0-1632971552772.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313881i5BDD0C1B78447D32%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22tikitour_0-1632971552772.png%22%20alt%3D%22tikitour_0-1632971552772.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20location%20cell%20B3%20%3D%20Aus%2C%20and%20cell%20B4%20%3D%20Use%201%2C%20then%20answer%20cell%20will%20display%20Group%201%3C%2FP%3E%3CP%3EIf%20location%20cell%20B3%20%3D%20NZ%2C%20and%20cell%20B4%20%3D%20Use%204%2C%20then%20answer%20cell%20will%20display%20Group%203%3C%2FP%3E%3CP%3EIf%20location%20cell%20B3%20%3D%20Aus%2C%20and%20cell%20B4%20%3D%20Use%204%2C%20then%20answer%20cell%20will%20display%20Group%202%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20table%20to%20incorporate%20vlookup%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2797648%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2797679%22%20slang%3D%22en-US%22%3ERe%3A%20Functions%20and%20Formulas%2C%20which%20is%20best%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2797679%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1170731%22%20target%3D%22_blank%22%3E%40tikitour%3C%2FA%3E%26nbsp%3BIn%20it's%20roughest%20form%20that%20would%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(%24B%2411%3A%24C%2414%2CMATCH(B4%2C%24A%2411%3A%24A%2414%2C0)%2CMATCH(B3%2C%24B%2410%3A%24C%2410%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYou%20could%20make%20it%20easier%20to%20write%2C%20read%20and%20maintain%20if%20you%20would%20replace%20all%20the%20range%20references%20by%20named%20ranges.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

What function and formula do I use to generate the desired answer.

 

tikitour_0-1632971552772.png

 

 

If location cell B3 = Aus, and cell B4 = Use 1, then answer cell will display Group 1

If location cell B3 = NZ, and cell B4 = Use 4, then answer cell will display Group 3

If location cell B3 = Aus, and cell B4 = Use 4, then answer cell will display Group 2

  

I have created a table to incorporate vlookup function.

 

Thanks

2 Replies

@tikitour In it's roughest form that would be:

=INDEX($B$11:$C$14,MATCH(B4,$A$11:$A$14,0),MATCH(B3,$B$10:$C$10,0))

You could make it easier to write, read and maintain if you would replace all the range references by named ranges. 

best response confirmed by tikitour (New Contributor)
Solution

@Riny_van_Eekelen Perfect, it has worked, thank you, I would never have got that on my own.