Home

Excel help

%3CLINGO-SUB%20id%3D%22lingo-sub-483490%22%20slang%3D%22en-US%22%3EExcel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483490%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20experts%3C%2FP%3E%3CP%3EI%20need%20some%20help%2C%20been%20pluging%20around%20on%20google%20for%20hours%20and%20while%20got%20it%20close%20to%20work%2C%20can't%20get%20this%20nailed%20down.%3C%2FP%3E%3CP%3ESee%20attached%20file%3C%2FP%3E%3CP%3EFor%20column%20A%3AB%2C%20I%20am%20looking%20to%20input%20a%20formual%20in%20column%20E%20through%20H%20to%20caputre%20each%20example%20in%20column%20A%3AB%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%20tried%20to%20create%20a%20drop%20down%20list%20for%20each%20header%2C%20then%20do%20an%20array%20lookup%2C%20was%20semi%20sucesful%20but%20could%20not%20get%20it%20to%20ignore%20duplciates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20the%20above%20%2B%20attachment%20makes%20sense%20%3A)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-483490%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-484033%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-484033%22%20slang%3D%22en-US%22%3EIn%20F2%2C%20you%20may%20use%20this%20formula%2C%20copied%20down%20rows%20and%20across%20columns%3A%3CBR%20%2F%3E%3DINDEX(%24B%3A%24B%2C%3CBR%20%2F%3EMATCH(%24E2%2C%24B%3A%24B%2C0)%2B%3CBR%20%2F%3EMATCH(F%241%2C%24A%243%3A%24A%246%2C0))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489850%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489851%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489851%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20paste%20in%20exaclty%20as%20is%2C%20I%20think%20it%20not%20looking%20at%20the%20right%20cells.%3C%2FP%3E%3CP%3Eexample%2C%20shouldn't%20the%20formual%20start%20in%20column%20E2%20not%20F2%3F%26nbsp%3B%20Should%20be%20matching%20stating%20at%20A2%20not%20A3%2C%20see%20the%20screen%20shot%20attached%20of%20what%20happens%20when%20pasted%20as%20is.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489853%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489853%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20helping%2C%20here%20is%20the%20screen%20shot%20of%20what%20is%20happing%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%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%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110780iEA8CE0D0BF769B84%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221.png%22%20title%3D%221.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-490149%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-490149%22%20slang%3D%22en-US%22%3EThe%20formula%20I%20suggested%20presupposes%20that%20E2%20contains%20the%20lookup_value.%20You%20deleted%20the%20lookup_value%2C%20thereby%20effectively%20making%20blank%20cell%20as%20the%20lookup%20value.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-500382%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-500382%22%20slang%3D%22en-US%22%3E%3CP%3Eok%20NOW%20i%20see%2C%20small%20misunderstanding.%20What%20I%20was%20seeking%20was%20a%20formula%20to%20get%20the%20info%20from%20column%20B%20and%20populate%20into%20E%2C%20F%2C%20G%2C%20H%2C%20by%20matching%20the%20column%20headers%20compared%20to%20column%20A%20but%20not%20give%20duplicates.%20But%20not%20having%20to%20manually%20fill%20in%20column%20E%20(address).%20So%20that's%20why%20I%20deleted%20info%2C%20I%20thought%20the%20formula%20was%20going%20to%20do%20the%20work%20not%20dependent%20on%20manually%20typing%20in%20column%20E%3CBR%20%2F%3E%3CBR%20%2F%3Edoes%20that%20make%20sense%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
jcbuc78
New Contributor

Hello Excel experts

I need some help, been pluging around on google for hours and while got it close to work, can't get this nailed down.

See attached file

For column A:B, I am looking to input a formual in column E through H to caputre each example in column A:B

 

I had tried to create a drop down list for each header, then do an array lookup, was semi sucesful but could not get it to ignore duplciates.

 

Hope the above + attachment makes sense :) 

 

5 Replies
In F2, you may use this formula, copied down rows and across columns:
=INDEX($B:$B,
MATCH($E2,$B:$B,0)+
MATCH(F$1,$A$3:$A$6,0))

@Twifoo 

If paste in exaclty as is, I think it not looking at the right cells.

example, shouldn't the formual start in column E2 not F2?  Should be matching stating at A2 not A3, see the screen shot attached of what happens when pasted as is. 

Thank you for helping, here is the screen shot of what is happing

@Twifoo 

 

1.png

Highlighted
The formula I suggested presupposes that E2 contains the lookup_value. You deleted the lookup_value, thereby effectively making blank cell as the lookup value.

ok NOW i see, small misunderstanding. What I was seeking was a formula to get the info from column B and populate into E, F, G, H, by matching the column headers compared to column A but not give duplicates. But not having to manually fill in column E (address). So that's why I deleted info, I thought the formula was going to do the work not dependent on manually typing in column E

does that make sense?

 @Twifoo 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies