SOLVED

Functions to look at data in table and put it in cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2258185%22%20slang%3D%22en-US%22%3EFunctions%20to%20look%20at%20data%20in%20table%20and%20put%20it%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258185%22%20slang%3D%22en-US%22%3E%3CP%3EOperating%20System%3A%20Windows%2010%20Version%2020H2%2C%2064-bit%20operating%20system%3C%2FP%3E%3CP%3EExcel%20Product%3A%20Microsoft%20Office%20Professional%20Plus%202016%3CBR%20%2F%3E%3CBR%20%2F%3EHello.%20I'm%20new%20to%20Excel%20and%20don't%20know%20how%20to%20use%20function%20well.%3CBR%20%2F%3E%3CBR%20%2F%3EHere.%20I%20need%20to%20put%20Code%20in%20column%20E%20by%20look%20at%20data%20table%20on%20right%20side.%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222021-04-07%2012_27_33-Book2%20-%20Excel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F270668i12717025B97A985D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222021-04-07%2012_27_33-Book2%20-%20Excel.png%22%20alt%3D%222021-04-07%2012_27_33-Book2%20-%20Excel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2258185%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-2258209%22%20slang%3D%22en-US%22%3ERe%3A%20Functions%20to%20look%20at%20data%20in%20table%20and%20put%20it%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1018951%22%20target%3D%22_blank%22%3E%40Neimis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20suggest%20re-structuring%20the%20lookup%20table%20to%20look%20like%20this%20(I%20used%20a%20structured%20table%2C%20but%20you%20could%20change%20it%20back%20to%20a%20regular%20range%20if%20you%20wish%20(table%20tools%2Fconvert%20to%20range)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_0-1617775340290.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F270671i3D44C87B9089F1B7%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_0-1617775340290.png%22%20alt%3D%22JMB17_0-1617775340290.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20enter%20this%20in%20E2%20and%20hit%20Ctrl%2BShift%2BEnter%20(instead%20of%20just%20enter)%3A%3C%2FP%3E%3CP%3E%3DINDEX(Table1%5BCode%5D%2CMATCH(1%2C(Table1%5BType%5D%3DC2)*(Table1%5BColor%5D%3DD2)%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2258237%22%20slang%3D%22en-US%22%3ERe%3A%20Functions%20to%20look%20at%20data%20in%20table%20and%20put%20it%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258237%22%20slang%3D%22en-US%22%3EThank%20you%20!%3CBR%20%2F%3EYour%20table%20is%20easier%20to%20understand.%20It's%20clean%20and%20don't%20use%20much%20space.%3C%2FLINGO-BODY%3E
New Contributor

Operating System: Windows 10 Version 20H2, 64-bit operating system

Excel Product: Microsoft Office Professional Plus 2016

Hello. I'm new to Excel and don't know how to use function well.

Here. I need to put Code in column E by look at data table on right side.
2021-04-07 12_27_33-Book2 - Excel.png

3 Replies
best response confirmed by Neimis (New Contributor)
Solution

@Neimis 

 

I would suggest re-structuring the lookup table to look like this (I used a structured table, but you could change it back to a regular range if you wish (table tools/convert to range):

JMB17_0-1617775340290.png

 

Then, enter this in E2 and hit Ctrl+Shift+Enter (instead of just enter):

=INDEX(Table1[Code],MATCH(1,(Table1[Type]=C2)*(Table1[Color]=D2),0))

 

 

 

 

Thank you !
Your table is easier to understand. It's clean and don't use much space.
You are welcome.