• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match Header & a row value to get corresponding values

fortune

Member
My Data Range is B2:G9 (which contains values) with ‘HEADER’ in B1:G1 (Non -numeric)

switches’ are in B12:G12 which are EITHER 0 OR 1; AND at a time ONLY 1 ‘SWITCH‘ FOR A 'UNIQUE’ HEADER WILL HAVE 0 OR 1

UNIQUE’ HEADERS are in I1:J1

Output is required in I2:J9 as:
Match UNIQUE’ HEADERS with switch value equal to 1 and grab the corresponding value from B2:G9

How to accomplish?
Thanks in advance.
I am using Excel 2007.
 

Attachments

AlanSidman

Active Member
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.
 

Nebu

Excel Ninja
Hi:

May be this? Array formula:

=IFERROR(INDEX(B2:G2,MATCH(I1&1,$B$1:$G$1&$B$12:$G$12,0)),"")

Execute by pressing Control+Shift+Enter

Thanks
 

Attachments

fortune

Member
=IFERROR(INDEX(B2:G2,MATCH(I1&1,$B$1:$G$1&$B$12:$G$12,0)),"")
Thank Nebu for your prompt help....IT WORKS...

PLUS

Your formula DOES NOT DISTINGUISHES between "H" & "h"...i.e. will it be possible for the formula to be 'correct' enough to MATCH EXACTLY?
 
Last edited:

fortune

Member
=IFERROR(INDEX($B2:$G2,MATCH(TRUE,EXACT(L1&1,$B$1:$G$1&$B$12:$G$12),0)),"")
Thanks p45cal. IT WORKS. I made L1 as I1...
Just a question since the formula given by you is an array, will the formula work 'PROPERLY' if some columns are inserted before column I making column I to shift right & there by I1 will shift right to a 'new position' OR I would be required to change the formula every-time I insert columns before column I?
 

p45cal

Well-Known Member
will the formula work 'PROPERLY' if some columns are inserted before column I making column I to shift right & there by I1 will shift right to a 'new position' OR I would be required to change the formula every-time I insert columns before column I?
It should continue to work without adjustment - why not try it?
 

fortune

Member
It should continue to work without adjustment - why not try it?
Can I also get "" as answer, if MORE THAN ONE 1s are found in B12:G12 for EITHER of EACH corresponding header?
In my example,
i.e. if B12:G12 contains MORE THAN ONE 1s for each corresponding "H", the formula should return blank
& similarly,
i.e. if B12:G12 contains MORE THAN ONE 1s for each corresponding "L", the formula should return blank
 

p45cal

Well-Known Member
try (array-entered still):
Code:
=IF(SUM(1*(EXACT(I$1&1,$B$1:$G$1&$B$12:$G$12)))=1,INDEX($B2:$G2,MATCH(TRUE,EXACT(I$1&1,$B$1:$G$1&$B$12:$G$12),0)),"")
and if turns up errors you don't want to see put the whole formula in an IFERROR function.
 

fortune

Member
try (array-entered still):
Code:
=IF(SUM(1*(EXACT(I$1&1,$B$1:$G$1&$B$12:$G$12)))=1,INDEX($B2:$G2,MATCH(TRUE,EXACT(I$1&1,$B$1:$G$1&$B$12:$G$12),0)),"")
and if turns up errors you don't want to see put the whole formula in an IFERROR function.
This extended part would definitely help me more & make my Excel more flexible….
I12=1 & J12=1
The formula should check ‘Matching’ of I12 in B12:G12 & then proceed as per previous criteria’s……
 

fortune

Member
Explain some more because I don't understand at all.
Currently,

  • the formula looks for ‘A DEFINED VALUE1 in B12:G12
  • AND MUST OCCUR ONLY ONCE (in B12:G12)
  • MATCHES EXACTLY the INPUT HEADER (INPUT HEADER is B1:G1) with the OUTPUT RANGE’S HEADER (I1:J1)
In extended formula, I need


  • the formula should look for ‘VALUE’ in I12:J12 (new cells)
  • Value of I12 MUST OCCUR ONLY ONCE (in B12:G12) ELSE “” AND the formula should MATCH EXACTLY THE CORRESPONDING OUTPUT HEADER I1 with INPUT HEADERS (B1:G1) & grab the ‘corresponding’ value from B2:G9 (containing I12 in B12:G12)
  • Similarly, for J12 MUST OCCUR ONLY ONCE (in B12:G12) ELSE “” AND the formula should MATCH EXACTLY THE CORRESPONDING OUTPUT HEADER J1 with INPUT HEADERS (B1:G1) & grab the ‘corresponding’ value from B2:G9 (containing J12 in B12:G12)

  • Here I12 could be 100 & therefore 100 needs to be ‘checked’ for ONLY ONCE OCCURRENCE (in B12:G12) AND the formula should MATCH EXACTLY THE CORRESPONDING OUTPUT HEADER I1 with INPUT HEADERS (B1:G1) & grab the ‘corresponding’ value from B2:G9 (containing 100 in B12:G12)
 
Last edited:

p45cal

Well-Known Member
Try in cell I2, array entered as usual:
Code:
=IF(SUM(1*(EXACT(I$1&I$12,$B$1:$G$1&$B$12:$G$12)))=1,INDEX($B2:$G2,MATCH(TRUE,EXACT(I$1&I$12,$B$1:$G$1&$B$12:$G$12),0)),"")
which checks for the combination of rows 1 and 12 occurring only once or this:
Code:
=IF(SUM(1*(EXACT(I$12,$B$12:$G$12)))=1,INDEX($B2:$G2,MATCH(TRUE,EXACT(I$1&I$12,$B$1:$G$1&$B$12:$G$12),0)),"")
which only checks row 12 for unique values of I12 and pays no attention to row 1.
 

fortune

Member
Try in cell I2, array entered as usual:
Code:
=IF(SUM(1*(EXACT(I$1&I$12,$B$1:$G$1&$B$12:$G$12)))=1,INDEX($B2:$G2,MATCH(TRUE,EXACT(I$1&I$12,$B$1:$G$1&$B$12:$G$12),0)),"")
which checks for the combination of rows 1 and 12 occurring only once or this:
Code:
=IF(SUM(1*(EXACT(I$12,$B$12:$G$12)))=1,INDEX($B2:$G2,MATCH(TRUE,EXACT(I$1&I$12,$B$1:$G$1&$B$12:$G$12),0)),"")
which only checks row 12 for unique values of I12 and pays no attention to row 1.
The FIRST formula........with combination IS SUITABLE; EXCEPT when I12 is 0. Formula works for -ve & +ve I12 but not when I12=0
Can the formula be made adaptable to consider I12=0 also. This would solve my problem
 
Last edited:

fortune

Member
Are you sure you haven't got 2 zeroes in row 12?
There CAN be MORE THAN ONE 0s in B12:G12

The formula has to ‘deal’ in SIMILAR WAY as it ‘deals’ with 1s or Any Other Numbers in B12:G12 i.e.

  • It has to ‘look for’ value in I12 & IF I12=0 than
  • IF MORE THAN ONE 0 AGAINST ‘distinct’ EXACT header (B1:G1); it should return “” ELSE grab the value from B2:G9
 

bosco_yip

Excel Ninja
63365

In I2, copied to J2 and all copied down :

=IFERROR(1/(1/INDEX($B2:$G2,AGGREGATE(15,6,COLUMN($B$1:$G$1)-COLUMN($A$1)/($B$12:$G$12=AGGREGATE(14,6,$B$12:$G$12/($B$1:$G$1=I$1),1))/($B$1:$G$1=I$1),1))),"")

Regards
Bosco
 

p45cal

Well-Known Member
You're not following your own rules! There are 3 pairs of H with zero (columns B, D and F):
IF MORE THAN ONE 0 AGAINST ‘distinct’ EXACT header (B1:G1); it should return “”
Remove 2 of those zeroes and you'll get the result you want.
 

fortune

Member
You're not following your own rules! There are 3 pairs of H with zero (columns B, D and F):
IF MORE THAN ONE 0 AGAINST ‘distinct’ EXACT header (B1:G1); it should return “”
Remove 2 of those zeroes and you'll get the result you want.
My mistake...APOLOGY......Both formulas WORK...Thanks p45cal for the help rendered
 

fortune

Member
Please see attached file as per above (post #21) reply

Regards
Bosco
I may be that I do not know how your formula works…
I downloaded the file & JUST CHANGED B12 to 1 from 0
Since I12=0, the I2 SHOULD HAVE BECOME “” & similarly the I4 SHOULD HAVE BECOME “”.
But the value is UNCHANGED i.e. I2=100 & I4=111 WHICH IS WRONG.

For your necessary comments & action please.
 
Last edited:
Top