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
Highlighted
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
Highlighted
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))
Highlighted

@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. 

Highlighted

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

@Twifoo 

 

1.png

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.
Highlighted

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
Linking
Rick0302 in Excel on
0 Replies
Anyone good at custom format? Please help
Kaneszc in Excel on
1 Replies
Show Blank or Zero
I_B_C in Excel on
2 Replies
Duplicate Billing
Kay_T1060 in Excel on
1 Replies
formuale help
Gavc1 in Excel on
1 Replies
searching a sheet with a true and false function
Aaron_excel in Excel on
5 Replies