# 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

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?
I am using Excel 2007.

#### Attachments

• 7.5 KB Views: 14

#### 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

• 9.4 KB Views: 15

#### 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:

#### p45cal

##### Well-Known Member
Array-entered:
=IFERROR(INDEX(\$B2:\$G2,MATCH(TRUE,EXACT(L1&1,\$B\$1:\$G\$1&\$B\$12:\$G\$12),0)),"")

#### 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
=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)),"")
Thanks p45cal. IT WORKS

#### 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……

#### p45cal

##### Well-Known Member
Explain some more because I don't understand at all.

#### 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)
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:

#### fortune

##### Member
In short, the formula INSTEAD for looking a FIXED VALUE of 1 should become 'flexible' to look value at I12

#### 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:

#### p45cal

##### Well-Known Member
Are you sure you haven't got 2 zeroes in row 12?

#### 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

#### p45cal

##### Well-Known Member
Please supply a workbook which uses the first formula in msg#15 and fouls up with the zeroes.

#### fortune

##### Member
Please supply a workbook which uses the first formula in msg#15 and fouls up with the zeroes.
Attached

#### Attachments

• 8.9 KB Views: 4

#### bosco_yip

##### Excel Ninja

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

Regards
Bosco

#### Attachments

• 11.6 KB Views: 3

#### 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