Finding specific text in a row/column, then using the column/row for that found row/column [closed]
up vote
0
down vote
favorite
Given the table in the scenario image attached, how best to go around achieving the search functionality by person and by food type, either returning the cell value (i.e. 'y' or blank) or the related header value (i.e. persons name or food type)?
To clarify, the questions the searches are asking are:
- Who had foodA?
- What did personB have to eat?
microsoft-excel
edited Nov 28 at 16:28
Mureinik
2,25151525
asked Nov 28 at 13:24
shtw
13
closed as unclear what you're asking by bertieb, PeterH, Dave, Mike Fitzpatrick, BillP3rd Nov 30 at 2:09
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
0
down vote
favorite
Given the table in the scenario image attached, how best to go around achieving the search functionality by person and by food type, either returning the cell value (i.e. 'y' or blank) or the related header value (i.e. persons name or food type)?
To clarify, the questions the searches are asking are:
- Who had foodA?
- What did personB have to eat?
microsoft-excel
edited Nov 28 at 16:28
Mureinik
2,25151525
asked Nov 28 at 13:24
shtw
13
closed as unclear what you're asking by bertieb, PeterH, Dave, Mike Fitzpatrick, BillP3rd Nov 30 at 2:09
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
What formulas do you have and which ones aren't working?
– Mark Fitzgerald
Nov 28 at 14:01
Hey Mark, no formulas as I wasn't sure how to best resolve this. Please see my latest comment below Forward Ed's reply for clarification (starts "Thanks again for the replies...")
– shtw
Nov 28 at 15:55
No formulas? Are you really asking us to do your work? It is best to show what you have tried as it helps is to understand. As it is I have voted to close
– Dave
Nov 28 at 20:14
Showing what I've tried wouldn't help, Dave, as I've no idea where to start with this problem. Hence asking for advice given as clear a scenario as I can outline.
– shtw
Nov 29 at 10:23
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Given the table in the scenario image attached, how best to go around achieving the search functionality by person and by food type, either returning the cell value (i.e. 'y' or blank) or the related header value (i.e. persons name or food type)?
To clarify, the questions the searches are asking are:
- Who had foodA?
- What did personB have to eat?
microsoft-excel
edited Nov 28 at 16:28
Mureinik
2,25151525
asked Nov 28 at 13:24
shtw
13
Given the table in the scenario image attached, how best to go around achieving the search functionality by person and by food type, either returning the cell value (i.e. 'y' or blank) or the related header value (i.e. persons name or food type)?
To clarify, the questions the searches are asking are:
- Who had foodA?
- What did personB have to eat?
microsoft-excel
microsoft-excel
edited Nov 28 at 16:28
Mureinik
2,25151525
asked Nov 28 at 13:24
shtw
13
edited Nov 28 at 16:28
Mureinik
2,25151525
asked Nov 28 at 13:24
shtw
13
edited Nov 28 at 16:28
Mureinik
2,25151525
edited Nov 28 at 16:28
Mureinik
2,25151525
edited Nov 28 at 16:28
Mureinik
2,25151525
2,25151525
asked Nov 28 at 13:24
shtw
13
asked Nov 28 at 13:24
shtw
13
asked Nov 28 at 13:24
shtw
13
13
closed as unclear what you're asking by bertieb, PeterH, Dave, Mike Fitzpatrick, BillP3rd Nov 30 at 2:09
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as unclear what you're asking by bertieb, PeterH, Dave, Mike Fitzpatrick, BillP3rd Nov 30 at 2:09
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
What formulas do you have and which ones aren't working?
– Mark Fitzgerald
Nov 28 at 14:01
Hey Mark, no formulas as I wasn't sure how to best resolve this. Please see my latest comment below Forward Ed's reply for clarification (starts "Thanks again for the replies...")
– shtw
Nov 28 at 15:55
No formulas? Are you really asking us to do your work? It is best to show what you have tried as it helps is to understand. As it is I have voted to close
– Dave
Nov 28 at 20:14
Showing what I've tried wouldn't help, Dave, as I've no idea where to start with this problem. Hence asking for advice given as clear a scenario as I can outline.
– shtw
Nov 29 at 10:23
add a comment |
What formulas do you have and which ones aren't working?
– Mark Fitzgerald
Nov 28 at 14:01
Hey Mark, no formulas as I wasn't sure how to best resolve this. Please see my latest comment below Forward Ed's reply for clarification (starts "Thanks again for the replies...")
– shtw
Nov 28 at 15:55
No formulas? Are you really asking us to do your work? It is best to show what you have tried as it helps is to understand. As it is I have voted to close
– Dave
Nov 28 at 20:14
Showing what I've tried wouldn't help, Dave, as I've no idea where to start with this problem. Hence asking for advice given as clear a scenario as I can outline.
– shtw
Nov 29 at 10:23
What formulas do you have and which ones aren't working?
– Mark Fitzgerald
Nov 28 at 14:01
What formulas do you have and which ones aren't working?
– Mark Fitzgerald
Nov 28 at 14:01
Hey Mark, no formulas as I wasn't sure how to best resolve this. Please see my latest comment below Forward Ed's reply for clarification (starts "Thanks again for the replies...")
– shtw
Nov 28 at 15:55
Hey Mark, no formulas as I wasn't sure how to best resolve this. Please see my latest comment below Forward Ed's reply for clarification (starts "Thanks again for the replies...")
– shtw
Nov 28 at 15:55
No formulas? Are you really asking us to do your work? It is best to show what you have tried as it helps is to understand. As it is I have voted to close
– Dave
Nov 28 at 20:14
No formulas? Are you really asking us to do your work? It is best to show what you have tried as it helps is to understand. As it is I have voted to close
– Dave
Nov 28 at 20:14
Showing what I've tried wouldn't help, Dave, as I've no idea where to start with this problem. Hence asking for advice given as clear a scenario as I can outline.
– shtw
Nov 29 at 10:23
Showing what I've tried wouldn't help, Dave, as I've no idea where to start with this problem. Hence asking for advice given as clear a scenario as I can outline.
– shtw
Nov 29 at 10:23
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
=IFERROR(INDEX($A$1:$D$6,MATCH(H2,$A$1:$A$6,0),MATCH($G$1,$A$1:$D$1,0)),"")
Place that in G1 and copy down It will return the value of the intersecting matching columns and rows. without the IFERROR it would throw an error if nothing is found. alternatively you can change the "" to "NOT FOUND"
The above formula will return 0 for the blank cells. If this is not desirable you have a couple of options. One is to put the formula into an IF function and test it for a zero result and then either display "" if the result was zero or repeat the formula to get the formula's result.
=IFERROR(IF(INDEX($A$1:$D$6,MATCH($H2,$A$1:$A$6,0),MATCH(G$1,$A$1:$D$1,0))=0,"",INDEX($A$1:$D$6,MATCH($H2,$A$1:$A$6,0),MATCH(G$1,$A$1:$D$1,0))),"")
Alternatively you could go to custom formatting for the cell and apply a format that would display "" for values of 0
It is the same formula for B1. You just need to adjust your reference cells and change the $ locations as you will be copying to the right instead of down like before. In B1 the rearranged formula would look like:
=IFERROR(IF(INDEX($A$1:$D$6,MATCH($A9,$A$1:$A$6,0),MATCH(B$10,$A$1:$D$1,0))=0,"",INDEX($A$1:$D$6,MATCH($A9,$A$1:$A$6,0),MATCH(B$10,$A$1:$D$1,0))),"")
answered Nov 28 at 13:36
Forward Ed
461213
Thanks for the reply. That results in a column of zeros. EDIT: It changes to blanks once a search term is entered into G1. However, all blanks, nothing is 'found' for e.g. bacon.
– shtw
Nov 28 at 13:42
This is very strange. Depending on the search term, it either returns all blanks, half-correct results. And in the case of a search term where that word does not exist (e.g. ham), it returns false positives. Other non-existing (not column headers) words don't have the same effect (e.g. search term 'beans' returns all blank, 'tomato' returns a mixture of blanks, y, and a zero!).
– shtw
Nov 28 at 13:55
Sorry missed the search by name at the bottom
– Forward Ed
Nov 28 at 14:35
Thanks again for the replies. It still gives random results. Perhaps I wasn't totally clear with the scenario.. - The table is pre-populated with 'y' or blanks - The search terms (red text) are blank - The results (green) are blank - User enters a value in a search term cell (e.g. bacon) - The results cells (e.g. in columns G & H (green text)) are now updated - Entering new search terms updates the results Same behaviour for the other search cell. I've updated the image in my OP to have green text.
– shtw
Nov 28 at 15:10
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
=IFERROR(INDEX($A$1:$D$6,MATCH(H2,$A$1:$A$6,0),MATCH($G$1,$A$1:$D$1,0)),"")
Place that in G1 and copy down It will return the value of the intersecting matching columns and rows. without the IFERROR it would throw an error if nothing is found. alternatively you can change the "" to "NOT FOUND"
The above formula will return 0 for the blank cells. If this is not desirable you have a couple of options. One is to put the formula into an IF function and test it for a zero result and then either display "" if the result was zero or repeat the formula to get the formula's result.
=IFERROR(IF(INDEX($A$1:$D$6,MATCH($H2,$A$1:$A$6,0),MATCH(G$1,$A$1:$D$1,0))=0,"",INDEX($A$1:$D$6,MATCH($H2,$A$1:$A$6,0),MATCH(G$1,$A$1:$D$1,0))),"")
Alternatively you could go to custom formatting for the cell and apply a format that would display "" for values of 0
It is the same formula for B1. You just need to adjust your reference cells and change the $ locations as you will be copying to the right instead of down like before. In B1 the rearranged formula would look like:
=IFERROR(IF(INDEX($A$1:$D$6,MATCH($A9,$A$1:$A$6,0),MATCH(B$10,$A$1:$D$1,0))=0,"",INDEX($A$1:$D$6,MATCH($A9,$A$1:$A$6,0),MATCH(B$10,$A$1:$D$1,0))),"")
answered Nov 28 at 13:36
Forward Ed
461213
Thanks for the reply. That results in a column of zeros. EDIT: It changes to blanks once a search term is entered into G1. However, all blanks, nothing is 'found' for e.g. bacon.
– shtw
Nov 28 at 13:42
This is very strange. Depending on the search term, it either returns all blanks, half-correct results. And in the case of a search term where that word does not exist (e.g. ham), it returns false positives. Other non-existing (not column headers) words don't have the same effect (e.g. search term 'beans' returns all blank, 'tomato' returns a mixture of blanks, y, and a zero!).
– shtw
Nov 28 at 13:55
Sorry missed the search by name at the bottom
– Forward Ed
Nov 28 at 14:35
Thanks again for the replies. It still gives random results. Perhaps I wasn't totally clear with the scenario.. - The table is pre-populated with 'y' or blanks - The search terms (red text) are blank - The results (green) are blank - User enters a value in a search term cell (e.g. bacon) - The results cells (e.g. in columns G & H (green text)) are now updated - Entering new search terms updates the results Same behaviour for the other search cell. I've updated the image in my OP to have green text.
– shtw
Nov 28 at 15:10
add a comment |
up vote
0
down vote
=IFERROR(INDEX($A$1:$D$6,MATCH(H2,$A$1:$A$6,0),MATCH($G$1,$A$1:$D$1,0)),"")
Place that in G1 and copy down It will return the value of the intersecting matching columns and rows. without the IFERROR it would throw an error if nothing is found. alternatively you can change the "" to "NOT FOUND"
The above formula will return 0 for the blank cells. If this is not desirable you have a couple of options. One is to put the formula into an IF function and test it for a zero result and then either display "" if the result was zero or repeat the formula to get the formula's result.
=IFERROR(IF(INDEX($A$1:$D$6,MATCH($H2,$A$1:$A$6,0),MATCH(G$1,$A$1:$D$1,0))=0,"",INDEX($A$1:$D$6,MATCH($H2,$A$1:$A$6,0),MATCH(G$1,$A$1:$D$1,0))),"")
Alternatively you could go to custom formatting for the cell and apply a format that would display "" for values of 0
It is the same formula for B1. You just need to adjust your reference cells and change the $ locations as you will be copying to the right instead of down like before. In B1 the rearranged formula would look like:
=IFERROR(IF(INDEX($A$1:$D$6,MATCH($A9,$A$1:$A$6,0),MATCH(B$10,$A$1:$D$1,0))=0,"",INDEX($A$1:$D$6,MATCH($A9,$A$1:$A$6,0),MATCH(B$10,$A$1:$D$1,0))),"")
answered Nov 28 at 13:36
Forward Ed
461213
Thanks for the reply. That results in a column of zeros. EDIT: It changes to blanks once a search term is entered into G1. However, all blanks, nothing is 'found' for e.g. bacon.
– shtw
Nov 28 at 13:42
This is very strange. Depending on the search term, it either returns all blanks, half-correct results. And in the case of a search term where that word does not exist (e.g. ham), it returns false positives. Other non-existing (not column headers) words don't have the same effect (e.g. search term 'beans' returns all blank, 'tomato' returns a mixture of blanks, y, and a zero!).
– shtw
Nov 28 at 13:55
Sorry missed the search by name at the bottom
– Forward Ed
Nov 28 at 14:35
Thanks again for the replies. It still gives random results. Perhaps I wasn't totally clear with the scenario.. - The table is pre-populated with 'y' or blanks - The search terms (red text) are blank - The results (green) are blank - User enters a value in a search term cell (e.g. bacon) - The results cells (e.g. in columns G & H (green text)) are now updated - Entering new search terms updates the results Same behaviour for the other search cell. I've updated the image in my OP to have green text.
– shtw
Nov 28 at 15:10
add a comment |
up vote
0
down vote
up vote
0
down vote
=IFERROR(INDEX($A$1:$D$6,MATCH(H2,$A$1:$A$6,0),MATCH($G$1,$A$1:$D$1,0)),"")
Place that in G1 and copy down It will return the value of the intersecting matching columns and rows. without the IFERROR it would throw an error if nothing is found. alternatively you can change the "" to "NOT FOUND"
The above formula will return 0 for the blank cells. If this is not desirable you have a couple of options. One is to put the formula into an IF function and test it for a zero result and then either display "" if the result was zero or repeat the formula to get the formula's result.
=IFERROR(IF(INDEX($A$1:$D$6,MATCH($H2,$A$1:$A$6,0),MATCH(G$1,$A$1:$D$1,0))=0,"",INDEX($A$1:$D$6,MATCH($H2,$A$1:$A$6,0),MATCH(G$1,$A$1:$D$1,0))),"")
Alternatively you could go to custom formatting for the cell and apply a format that would display "" for values of 0
It is the same formula for B1. You just need to adjust your reference cells and change the $ locations as you will be copying to the right instead of down like before. In B1 the rearranged formula would look like:
=IFERROR(IF(INDEX($A$1:$D$6,MATCH($A9,$A$1:$A$6,0),MATCH(B$10,$A$1:$D$1,0))=0,"",INDEX($A$1:$D$6,MATCH($A9,$A$1:$A$6,0),MATCH(B$10,$A$1:$D$1,0))),"")
answered Nov 28 at 13:36
Forward Ed
461213
=IFERROR(INDEX($A$1:$D$6,MATCH(H2,$A$1:$A$6,0),MATCH($G$1,$A$1:$D$1,0)),"")
Place that in G1 and copy down It will return the value of the intersecting matching columns and rows. without the IFERROR it would throw an error if nothing is found. alternatively you can change the "" to "NOT FOUND"
The above formula will return 0 for the blank cells. If this is not desirable you have a couple of options. One is to put the formula into an IF function and test it for a zero result and then either display "" if the result was zero or repeat the formula to get the formula's result.
=IFERROR(IF(INDEX($A$1:$D$6,MATCH($H2,$A$1:$A$6,0),MATCH(G$1,$A$1:$D$1,0))=0,"",INDEX($A$1:$D$6,MATCH($H2,$A$1:$A$6,0),MATCH(G$1,$A$1:$D$1,0))),"")
Alternatively you could go to custom formatting for the cell and apply a format that would display "" for values of 0
It is the same formula for B1. You just need to adjust your reference cells and change the $ locations as you will be copying to the right instead of down like before. In B1 the rearranged formula would look like:
=IFERROR(IF(INDEX($A$1:$D$6,MATCH($A9,$A$1:$A$6,0),MATCH(B$10,$A$1:$D$1,0))=0,"",INDEX($A$1:$D$6,MATCH($A9,$A$1:$A$6,0),MATCH(B$10,$A$1:$D$1,0))),"")
answered Nov 28 at 13:36
Forward Ed
461213
edited Nov 28 at 16:11
answered Nov 28 at 13:36
Forward Ed
461213
answered Nov 28 at 13:36
Forward Ed
461213
answered Nov 28 at 13:36
Forward Ed
461213
461213
Thanks for the reply. That results in a column of zeros. EDIT: It changes to blanks once a search term is entered into G1. However, all blanks, nothing is 'found' for e.g. bacon.
– shtw
Nov 28 at 13:42
This is very strange. Depending on the search term, it either returns all blanks, half-correct results. And in the case of a search term where that word does not exist (e.g. ham), it returns false positives. Other non-existing (not column headers) words don't have the same effect (e.g. search term 'beans' returns all blank, 'tomato' returns a mixture of blanks, y, and a zero!).
– shtw
Nov 28 at 13:55
Sorry missed the search by name at the bottom
– Forward Ed
Nov 28 at 14:35
Thanks again for the replies. It still gives random results. Perhaps I wasn't totally clear with the scenario.. - The table is pre-populated with 'y' or blanks - The search terms (red text) are blank - The results (green) are blank - User enters a value in a search term cell (e.g. bacon) - The results cells (e.g. in columns G & H (green text)) are now updated - Entering new search terms updates the results Same behaviour for the other search cell. I've updated the image in my OP to have green text.
– shtw
Nov 28 at 15:10
add a comment |
Thanks for the reply. That results in a column of zeros. EDIT: It changes to blanks once a search term is entered into G1. However, all blanks, nothing is 'found' for e.g. bacon.
– shtw
Nov 28 at 13:42
This is very strange. Depending on the search term, it either returns all blanks, half-correct results. And in the case of a search term where that word does not exist (e.g. ham), it returns false positives. Other non-existing (not column headers) words don't have the same effect (e.g. search term 'beans' returns all blank, 'tomato' returns a mixture of blanks, y, and a zero!).
– shtw
Nov 28 at 13:55
Sorry missed the search by name at the bottom
– Forward Ed
Nov 28 at 14:35
Thanks again for the replies. It still gives random results. Perhaps I wasn't totally clear with the scenario.. - The table is pre-populated with 'y' or blanks - The search terms (red text) are blank - The results (green) are blank - User enters a value in a search term cell (e.g. bacon) - The results cells (e.g. in columns G & H (green text)) are now updated - Entering new search terms updates the results Same behaviour for the other search cell. I've updated the image in my OP to have green text.
– shtw
Nov 28 at 15:10
Thanks for the reply. That results in a column of zeros. EDIT: It changes to blanks once a search term is entered into G1. However, all blanks, nothing is 'found' for e.g. bacon.
– shtw
Nov 28 at 13:42
Thanks for the reply. That results in a column of zeros. EDIT: It changes to blanks once a search term is entered into G1. However, all blanks, nothing is 'found' for e.g. bacon.
– shtw
Nov 28 at 13:42
This is very strange. Depending on the search term, it either returns all blanks, half-correct results. And in the case of a search term where that word does not exist (e.g. ham), it returns false positives. Other non-existing (not column headers) words don't have the same effect (e.g. search term 'beans' returns all blank, 'tomato' returns a mixture of blanks, y, and a zero!).
– shtw
Nov 28 at 13:55
This is very strange. Depending on the search term, it either returns all blanks, half-correct results. And in the case of a search term where that word does not exist (e.g. ham), it returns false positives. Other non-existing (not column headers) words don't have the same effect (e.g. search term 'beans' returns all blank, 'tomato' returns a mixture of blanks, y, and a zero!).
– shtw
Nov 28 at 13:55
Sorry missed the search by name at the bottom
– Forward Ed
Nov 28 at 14:35
Sorry missed the search by name at the bottom
– Forward Ed
Nov 28 at 14:35
Thanks again for the replies. It still gives random results. Perhaps I wasn't totally clear with the scenario.. - The table is pre-populated with 'y' or blanks - The search terms (red text) are blank - The results (green) are blank - User enters a value in a search term cell (e.g. bacon) - The results cells (e.g. in columns G & H (green text)) are now updated - Entering new search terms updates the results Same behaviour for the other search cell. I've updated the image in my OP to have green text.
– shtw
Nov 28 at 15:10
Thanks again for the replies. It still gives random results. Perhaps I wasn't totally clear with the scenario.. - The table is pre-populated with 'y' or blanks - The search terms (red text) are blank - The results (green) are blank - User enters a value in a search term cell (e.g. bacon) - The results cells (e.g. in columns G & H (green text)) are now updated - Entering new search terms updates the results Same behaviour for the other search cell. I've updated the image in my OP to have green text.
– shtw
Nov 28 at 15:10
add a comment |
What formulas do you have and which ones aren't working?
– Mark Fitzgerald
Nov 28 at 14:01
Hey Mark, no formulas as I wasn't sure how to best resolve this. Please see my latest comment below Forward Ed's reply for clarification (starts "Thanks again for the replies...")
– shtw
Nov 28 at 15:55
No formulas? Are you really asking us to do your work? It is best to show what you have tried as it helps is to understand. As it is I have voted to close
– Dave
Nov 28 at 20:14
Showing what I've tried wouldn't help, Dave, as I've no idea where to start with this problem. Hence asking for advice given as clear a scenario as I can outline.
– shtw
Nov 29 at 10:23