SOLVED
Home

How to search a table/colum with 2 hits

%3CLINGO-SUB%20id%3D%22lingo-sub-616157%22%20slang%3D%22en-US%22%3EHow%20to%20search%20a%20table%2Fcolum%20with%202%20hits%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-616157%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20a%20table%20with%20Products%20and%20machines.%20If%20there%20is%20an%20%22X%22%20the%20machine%20could%20be%20use%20for%20this%20product.%3C%2FP%3E%3CP%3EI%20want%20to%20search%20for%20the%20Product%20and%20give%20out%20the%20possible%20machine%20Name.%20Possible%20machine%20is%20Marked%20with%20an%20%22x%22%3C%2FP%3E%3CP%3EFor%20Example%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114414i9BEA6197E8775CE7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Input.PNG%22%20title%3D%22Input.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOutput%20should%20be%20like%20shown%20in%20the%20Picture.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20323px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114413iFC806058E394CF16%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Output.PNG%22%20title%3D%22Output.PNG%22%20%2F%3E%3C%2FSPAN%3EHow%20can%20i%20do%20this%3F%3C%2FP%3E%3CP%3EWhat%20Formula%20do%20i%20Need%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-616157%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-616312%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20search%20a%20table%2Fcolum%20with%202%20hits%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-616312%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F255898%22%20target%3D%22_blank%22%3E%40DKoh%3C%2FA%3E%26nbsp%3B%2C%20with%20model%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20471px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114428iFE10CF3ECA59EF38%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20use%20in%20B3%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%241%3A%241%2CAGGREGATE(15%2C6%2C1%2F(%24B2%3A%24AA2%3D%22x%22)*(COLUMN(%24B%242%3A%24AA%242)-COLUMN(%24A%245)%2B1)%2C%20(COLUMN()-COLUMN(%24A%245))))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3E%2C%20drag%20it%20to%20the%20right%20till%20empty%20cells%20appear%20and%20when%20to%20the%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-663271%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20search%20a%20table%2Fcolum%20with%202%20hits%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-663271%22%20slang%3D%22en-US%22%3E%3CP%3E%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%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20explain%20the%20formula%3C%2FP%3E%3CP%3EThat%20would%20be%20great%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677815%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20search%20a%20table%2Fcolum%20with%202%20hits%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677815%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F255898%22%20target%3D%22_blank%22%3E%40DKoh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20missed%20that%20your%20question.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EINDEX%20returns%20the%20value%20of%20the%20cell%20which%20is%20in%20entire%20(third%20parameter%20missed%20or%20zero)%20row%20(first%20parameter)%20on%20position%20defined%20with%20second%20parameter.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20find%20that%20position%20using%20AGGREGATE.%20It%20return%20Nth%20(fourth%20parameter)%20smallest%20(first%20parameter%2015)%20from%20the%20array%20(third%20parameter)%20ignoring%20all%20errors%20(second%20parameter%206).%26nbsp%3B%20As%20an%20array%20we%20take%20column%20numbers%20of%20your%20data%20range%20multiplying%20them%20on%201%2F%20logical%20check%20if%20the%20cell%20has%20the%20value%20%22x%22.%20Since%20TRUE%20is%20equal%20to%201%20and%20FALSE%20to%200%2C%20we%20have%20an%20array%20with%20ignoring%20errors%20or%201s.%20For%20the%20first%20current%20column%26nbsp%3B%204th%20parameters%20return%201%20(thus%20you%20find%20first%20smallest%20column%20number%20for%20the%20cell%20with%20%22x%22)%2C%20for%20the%20next%20cell%20it'll%20be%20second%20smallest%2C%20etc.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFinally%20we%20wrap%20entire%20formula%20with%20IFERROR%20to%20return%20empty%20string%20if%20Nth%20smallest%20is%20not%20found.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20stay%20on%20the%20cell%20with%20formula%20and%20check%20calculations%20by%20steps%20with%20Formula-Evaluate%20formula%20on%20ribbon.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPrevious%20time%20I%20overcomplicated%20the%20formula%20a%20bit%2C%20it%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%241%3A%241%2CAGGREGATE(15%2C6%2C1%2F(%24C2%3A%24AB2%3D%22x%22)*COLUMN(%24C%242%3A%24AB%242)%2C%20(COLUMN()-COLUMN(%24B%245))))%2C%22%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
DKoh
Occasional Contributor

Hello,

 

i have a table with Products and machines. If there is an "X" the machine could be use for this product.

I want to search for the Product and give out the possible machine Name. Possible machine is Marked with an "x"

For Example:Input.PNG

Output should be like shown in the Picture.

 

Output.PNGHow can i do this?

What Formula do i Need?

 

Thank you in advance

3 Replies
Solution

@DKoh , with model like this

image.png

you may use in B3

=IFERROR(INDEX($1:$1,AGGREGATE(15,6,1/($B2:$AA2="x")*(COLUMN($B$2:$AA$2)-COLUMN($A$5)+1), (COLUMN()-COLUMN($A$5)))),"")

, drag it to the right till empty cells appear and when to the down

Highlighted

@Sergei Baklan 

Please explain the formula

That would be great

@DKoh 

 

Sorry, I missed that your question.

 

INDEX returns the value of the cell which is in entire (third parameter missed or zero) row (first parameter) on position defined with second parameter.

 

We find that position using AGGREGATE. It return Nth (fourth parameter) smallest (first parameter 15) from the array (third parameter) ignoring all errors (second parameter 6).  As an array we take column numbers of your data range multiplying them on 1/ logical check if the cell has the value "x". Since TRUE is equal to 1 and FALSE to 0, we have an array with ignoring errors or 1s. For the first current column  4th parameters return 1 (thus you find first smallest column number for the cell with "x"), for the next cell it'll be second smallest, etc.

 

Finally we wrap entire formula with IFERROR to return empty string if Nth smallest is not found.

 

You may stay on the cell with formula and check calculations by steps with Formula-Evaluate formula on ribbon.

 

Previous time I overcomplicated the formula a bit, it could be like

=IFERROR(INDEX($1:$1,AGGREGATE(15,6,1/($C2:$AB2="x")*COLUMN($C$2:$AB$2), (COLUMN()-COLUMN($B$5)))),"")
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies