SOLVED

Match multiple criteria from Different Worksheets and return a Value

%3CLINGO-SUB%20id%3D%22lingo-sub-1520440%22%20slang%3D%22en-US%22%3EMatch%20multiple%20criteria%20from%20Different%20Worksheets%20and%20return%20a%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1520440%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20like%20to%20seek%20expertise%20on%20excel%20formulas.%3C%2FP%3E%3CP%3ELooking%20to%20match%20multiple%20criteria%20from%202%20worksheets%20and%20return%20a%20value.%3C%2FP%3E%3CP%3E1st%20picture%20below%20is%20from%201st%20worksheet%20(Sheet%201).%3C%2FP%3E%3CP%3E2nd%20picture%20below%20is%20from%202nd%20worksheet%20(Sheet%202).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECondition%3A%20e.g.%20If%20B2%20matches%20value%20in%20Column%20C%20of%20Sheet%201%20and%20C2%20matches%20any%20value%20from%20Column%20D%20to%20Column%20I%20of%20Sheet%201%2C%20then%20return%20C2.%20Else%20return%20Unavailable.%3C%2FP%3E%3CP%3ELooking%20for%20the%20right%20formula%20to%20match%20the%20above%20condition%20and%20return%20the%20expected%20result%20as%20indicated%20in%20yellow%20cell%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIncluded%20the%20excel%20file%20for%20reference.%20Thank%20you.%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%22gab2020_0-1594710335881.png%22%20style%3D%22width%3A%20427px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205145i224CD405EC9FA2BC%2Fimage-dimensions%2F427x64%3Fv%3D1.0%22%20width%3D%22427%22%20height%3D%2264%22%20title%3D%22gab2020_0-1594710335881.png%22%20alt%3D%22gab2020_0-1594710335881.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22gab2020_1-1594710360090.png%22%20style%3D%22width%3A%20546px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205147i66CB8DC4995B8F05%2Fimage-dimensions%2F546x56%3Fv%3D1.0%22%20width%3D%22546%22%20height%3D%2256%22%20title%3D%22gab2020_1-1594710360090.png%22%20alt%3D%22gab2020_1-1594710360090.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1520440%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-1521722%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20multiple%20criteria%20from%20Different%20Worksheets%20and%20return%20a%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521722%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722079%22%20target%3D%22_blank%22%3E%40gab2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20788px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205277iBAFD21926C92EF7E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ISNA(MATCH(C2%2CINDEX(Sheet1!D%3AI%2CMATCH(B2%2CSheet1!C%3AC%2C0)%2C0)%2C0))%2C%22Unavailable%22%2CC2)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1528379%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20multiple%20criteria%20from%20Different%20Worksheets%20and%20return%20a%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1528379%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%20It's%20working%20good.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1529679%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20multiple%20criteria%20from%20Different%20Worksheets%20and%20return%20a%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1529679%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722079%22%20target%3D%22_blank%22%3E%40gab2020%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, like to seek expertise on excel formulas.

Looking to match multiple criteria from 2 worksheets and return a value.

1st picture below is from 1st worksheet (Sheet 1).

2nd picture below is from 2nd worksheet (Sheet 2).

 

Condition: e.g. If B2 matches value in Column C of Sheet 1 and C2 matches any value from Column D to Column I of Sheet 1, then return C2. Else return Unavailable.

Looking for the right formula to match the above condition and return the expected result as indicated in yellow cell below.

 

Included the excel file for reference. Thank you.

 

gab2020_0-1594710335881.png

gab2020_1-1594710360090.png

 

3 Replies
Best Response confirmed by gab2020 (New Contributor)
Solution

@gab2020 

Here

image.png

it could be

=IF(ISNA(MATCH(C2,INDEX(Sheet1!D:I,MATCH(B2,Sheet1!C:C,0),0),0)),"Unavailable",C2)