• 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

F

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

  • Switch.xlsx
    7.5 KB · Views: 15
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.
 
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

  • Switch.xlsx
    9.4 KB · Views: 16
Nebu said:
=IFERROR(INDEX(B2:G2,MATCH(I1&1,$B$1:$G$1&$B$12:$G$12,0)),"")
Click to expand...
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:
p45cal said:
=IFERROR(INDEX($B2:$G2,MATCH(TRUE,EXACT(L1&1,$B$1:$G$1&$B$12:$G$12),0)),"")
Click to expand...
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?
 
fortune said:
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?
Click to expand...
It should continue to work without adjustment - why not try it?
 
p45cal said:
It should continue to work without adjustment - why not try it?
Click to expand...
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
 
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.
 
p45cal said:
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.
Click to expand...

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……
 
p45cal said:
Explain some more because I don't understand at all.
Click to expand...
Currently,

  • the formula looks for ‘A DEFINED VALUE’ 1 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:
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.
 
p45cal said:
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.
Click to expand...
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:
p45cal said:
Are you sure you haven't got 2 zeroes in row 12?
Click to expand...
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
 
Please supply a workbook which uses the first formula in msg#15 and fouls up with the zeroes.
 
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
 
fortune said:
Attached
Click to expand...
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.
 
p45cal said:
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.
Click to expand...
My mistake...APOLOGY......Both formulas WORK...Thanks p45cal for the help rendered
 
bosco_yip said:
Please see attached file as per above (post #21) reply

Regards
Bosco
Click to expand...
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:
You must log in or register to reply here.
Back
Top