Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
Hi. Spreadsheet A in Workbook 2 has various columns which match the cell
values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
Your Column O -- In what Sheet and Workbook is it in?
"Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
Oops sorry, Column O is in Spreadsheet A of Workbook 2
so i want something like this If cell value in column I equals any one cell in column range O2:O50 then show cell value in column I, otherwise leave blank I have sorted the range alphabetically if that helps Thanks for looking Chris "JMay" wrote in message ... Your Column O -- In what Sheet and Workbook is it in? "Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
There might be a better (and shorter) way, but in your cell C2 enter:
=IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 )),"",(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1)) ) << All in one cell And Copy down to C50; HTH "Tester" wrote in message : Oops sorry, Column O is in Spreadsheet A of Workbook 2 so i want something like this If cell value in column I equals any one cell in column range O2:O50 then show cell value in column I, otherwise leave blank I have sorted the range alphabetically if that helps Thanks for looking Chris "JMay" wrote in message ... Your Column O -- In what Sheet and Workbook is it in? "Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
Thanks for this, I've tried it but only get the content of the cell in
column I, regardless of whether it matches a value in the range O2:O50 or not. Chris "JMay" wrote in message ... There might be a better (and shorter) way, but in your cell C2 enter: =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 )),"",(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1)) ) << All in one cell And Copy down to C50; HTH "Tester" wrote in message : Oops sorry, Column O is in Spreadsheet A of Workbook 2 so i want something like this If cell value in column I equals any one cell in column range O2:O50 then show cell value in column I, otherwise leave blank I have sorted the range alphabetically if that helps Thanks for looking Chris "JMay" wrote in message ... Your Column O -- In what Sheet and Workbook is it in? "Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
Try an exact match then
=IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 0)),"",(INDEX($O$2:$O$50,M ATCH(I2,$O$2:$O$50,0),0))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Thanks for this, I've tried it but only get the content of the cell in column I, regardless of whether it matches a value in the range O2:O50 or not. Chris "JMay" wrote in message ... There might be a better (and shorter) way, but in your cell C2 enter: =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 )),"",(INDEX($O$2:$O$50,MA TCH(I2,$O$2:$O$50,0),1))) << All in one cell And Copy down to C50; HTH "Tester" wrote in message : Oops sorry, Column O is in Spreadsheet A of Workbook 2 so i want something like this If cell value in column I equals any one cell in column range O2:O50 then show cell value in column I, otherwise leave blank I have sorted the range alphabetically if that helps Thanks for looking Chris "JMay" wrote in message ... Your Column O -- In what Sheet and Workbook is it in? "Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
I understood that you wanted in Col C the Content of Col I only if It
(the content of Col I) could be matched with the content of Col O. Sorry, "Tester" wrote in message : Thanks for this, I've tried it but only get the content of the cell in column I, regardless of whether it matches a value in the range O2:O50 or not. Chris "JMay" wrote in message ... There might be a better (and shorter) way, but in your cell C2 enter: =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 )),"",(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1)) ) << All in one cell And Copy down to C50; HTH "Tester" wrote in message : Oops sorry, Column O is in Spreadsheet A of Workbook 2 so i want something like this If cell value in column I equals any one cell in column range O2:O50 then show cell value in column I, otherwise leave blank I have sorted the range alphabetically if that helps Thanks for looking Chris "JMay" wrote in message ... Your Column O -- In what Sheet and Workbook is it in? "Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
Bob: You used 10 as the column # in the ISNA portion
And 0 as the column in the Alternate portion. Since our Index-table is ONLY ONE Column maybe neither actual numbers Matter. Can you confirm? Jim "Bob Phillips" wrote in message : Try an exact match then =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 0)),"",(INDEX($O$2:$O$50,M ATCH(I2,$O$2:$O$50,0),0))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Thanks for this, I've tried it but only get the content of the cell in column I, regardless of whether it matches a value in the range O2:O50 or not. Chris "JMay" wrote in message ... There might be a better (and shorter) way, but in your cell C2 enter: =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 )),"",(INDEX($O$2:$O$50,MA TCH(I2,$O$2:$O$50,0),1))) << All in one cell And Copy down to C50; HTH "Tester" wrote in message : Oops sorry, Column O is in Spreadsheet A of Workbook 2 so i want something like this If cell value in column I equals any one cell in column range O2:O50 then show cell value in column I, otherwise leave blank I have sorted the range alphabetically if that helps Thanks for looking Chris "JMay" wrote in message ... Your Column O -- In what Sheet and Workbook is it in? "Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
I thank both of you for the effort and help you are trying to give me but
both your formulas give me an answer every time, i.e. the content of the column even if it doesn't match any cell in the range to check. Chris I have not used ISNA before, can it be used without a column number? "JMay" wrote in message ... Bob: You used 10 as the column # in the ISNA portion And 0 as the column in the Alternate portion. Since our Index-table is ONLY ONE Column maybe neither actual numbers Matter. Can you confirm? Jim "Bob Phillips" wrote in message : Try an exact match then =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 0)),"",(INDEX($O$2:$O$50,M ATCH(I2,$O$2:$O$50,0),0))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Thanks for this, I've tried it but only get the content of the cell in column I, regardless of whether it matches a value in the range O2:O50 or not. Chris "JMay" wrote in message ... There might be a better (and shorter) way, but in your cell C2 enter: =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 )),"",(INDEX($O$2:$O$50,MA TCH(I2,$O$2:$O$50,0),1))) << All in one cell And Copy down to C50; HTH "Tester" wrote in message : Oops sorry, Column O is in Spreadsheet A of Workbook 2 so i want something like this If cell value in column I equals any one cell in column range O2:O50 then show cell value in column I, otherwise leave blank I have sorted the range alphabetically if that helps Thanks for looking Chris "JMay" wrote in message ... Your Column O -- In what Sheet and Workbook is it in? "Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
My mistake Jim, I misread the post, you were already using exact matching.
You can reduce the test though, =IF(ISNA(MATCH(I2,$O$2:$O$50,0)),"",INDEX($O$2:$O$ 50,MATCH(I2,$O$2:$O$50,0), 0)) but then if you are searching for a value in a column, why not reyturn that value if matched =IF(ISNA(MATCH(I2,$O$2:$O$50,0)),"",I2) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "JMay" wrote in message ... Bob: You used 10 as the column # in the ISNA portion And 0 as the column in the Alternate portion. Since our Index-table is ONLY ONE Column maybe neither actual numbers Matter. Can you confirm? Jim "Bob Phillips" wrote in message : Try an exact match then =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 0)),"",(INDEX($O$2:$O$50,M ATCH(I2,$O$2:$O$50,0),0))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Thanks for this, I've tried it but only get the content of the cell in column I, regardless of whether it matches a value in the range O2:O50 or not. Chris "JMay" wrote in message ... There might be a better (and shorter) way, but in your cell C2 enter: =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 )),"",(INDEX($O$2:$O$50,MA TCH(I2,$O$2:$O$50,0),1))) << All in one cell And Copy down to C50; HTH "Tester" wrote in message : Oops sorry, Column O is in Spreadsheet A of Workbook 2 so i want something like this If cell value in column I equals any one cell in column range O2:O50 then show cell value in column I, otherwise leave blank I have sorted the range alphabetically if that helps Thanks for looking Chris "JMay" wrote in message ... Your Column O -- In what Sheet and Workbook is it in? "Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
Copy and paste here to show us the exact formula you have in the cell in
Column C which contains a Value that should not be displaying a value. Jim "Tester" wrote in message : I thank both of you for the effort and help you are trying to give me but both your formulas give me an answer every time, i.e. the content of the column even if it doesn't match any cell in the range to check. Chris I have not used ISNA before, can it be used without a column number? "JMay" wrote in message ... Bob: You used 10 as the column # in the ISNA portion And 0 as the column in the Alternate portion. Since our Index-table is ONLY ONE Column maybe neither actual numbers Matter. Can you confirm? Jim "Bob Phillips" wrote in message : Try an exact match then =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 0)),"",(INDEX($O$2:$O$50,M ATCH(I2,$O$2:$O$50,0),0))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Thanks for this, I've tried it but only get the content of the cell in column I, regardless of whether it matches a value in the range O2:O50 or not. Chris "JMay" wrote in message ... There might be a better (and shorter) way, but in your cell C2 enter: =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 )),"",(INDEX($O$2:$O$50,MA TCH(I2,$O$2:$O$50,0),1))) << All in one cell And Copy down to C50; HTH "Tester" wrote in message : Oops sorry, Column O is in Spreadsheet A of Workbook 2 so i want something like this If cell value in column I equals any one cell in column range O2:O50 then show cell value in column I, otherwise leave blank I have sorted the range alphabetically if that helps Thanks for looking Chris "JMay" wrote in message ... Your Column O -- In what Sheet and Workbook is it in? "Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
looking for one value in a range of values
Ok, chaps picture this:
a pratt sat at his pc, with a red face and higher than normal temperature, as he trys to find a way to apologise for wasting your time having used the formula you devised and it works. The error causing non listed names to appear was my own in that they were appearing in the list because i hadn't filtered them properly and that's why they were always appearing with your formulae. My sincere apologies for not checking my own work before querying yours. Chris "JMay" wrote in message ... I understood that you wanted in Col C the Content of Col I only if It (the content of Col I) could be matched with the content of Col O. Sorry, "Tester" wrote in message : Thanks for this, I've tried it but only get the content of the cell in column I, regardless of whether it matches a value in the range O2:O50 or not. Chris "JMay" wrote in message ... There might be a better (and shorter) way, but in your cell C2 enter: =IF(ISNA(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1 )),"",(INDEX($O$2:$O$50,MATCH(I2,$O$2:$O$50,0),1)) ) << All in one cell And Copy down to C50; HTH "Tester" wrote in message : Oops sorry, Column O is in Spreadsheet A of Workbook 2 so i want something like this If cell value in column I equals any one cell in column range O2:O50 then show cell value in column I, otherwise leave blank I have sorted the range alphabetically if that helps Thanks for looking Chris "JMay" wrote in message ... Your Column O -- In what Sheet and Workbook is it in? "Tester" wrote in message : Hi. Spreadsheet A in Workbook 2 has various columns which match the cell values in Spreadsheet H of Workbook 1. Column O is a control list of Suppliers names so spelling etc is exact throughout. On Spreadsheet A in Workbook 2 I have inserted a column C to check the value of Column I against Column O. If it finds a match in the list, I want it to show the name but if not, I want it to remain blank. Could someone help with this formula please? TIA Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extraction of max values from a range | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
How many values appear more than once in a range? | Excel Worksheet Functions | |||
How to move Y-axis values when X range is -a to +b | Charts and Charting in Excel | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |