| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching numbers and text
I have on spreadsheet that has a column with a list of single numbers or
letters the next column are times associated with those numbers and letters. I want to type in one of those numbers or letters and have it displayed in another worksheet the time associted with it. I have it half working that is displays the letters but is comes up with a #N/A when you type in a number to match. Here is what I am using. Any help would be appreciated! =INDEX(Codes!$A$2:$B$33,MATCH('F Codes'!G7,Codes!$A$2:$A$33,FALSE),2) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching numbers and text
Suggest you do a test to check if the numbers are actually text.
In a cell in F Codes sheet enter =G7=Codes!A12 (replace A12 by a cell that has a number). Type the same number into G7 as you see in Codes!A12; you should get TRUE as the result. Or on Codes sheet in D2 (or wherever) use =ISNUMBER(A2) and copy down to D33 But why not use VLOOKUP? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mndpy" wrote in message ... I have on spreadsheet that has a column with a list of single numbers or letters the next column are times associated with those numbers and letters. I want to type in one of those numbers or letters and have it displayed in another worksheet the time associted with it. I have it half working that is displays the letters but is comes up with a #N/A when you type in a number to match. Here is what I am using. Any help would be appreciated! =INDEX(Codes!$A$2:$B$33,MATCH('F Codes'!G7,Codes!$A$2:$A$33,FALSE),2) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching numbers and text
What lookup should I try?
"Bernard Liengme" wrote: Suggest you do a test to check if the numbers are actually text. In a cell in F Codes sheet enter =G7=Codes!A12 (replace A12 by a cell that has a number). Type the same number into G7 as you see in Codes!A12; you should get TRUE as the result. Or on Codes sheet in D2 (or wherever) use =ISNUMBER(A2) and copy down to D33 But why not use VLOOKUP? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mndpy" wrote in message ... I have on spreadsheet that has a column with a list of single numbers or letters the next column are times associated with those numbers and letters. I want to type in one of those numbers or letters and have it displayed in another worksheet the time associted with it. I have it half working that is displays the letters but is comes up with a #N/A when you type in a number to match. Here is what I am using. Any help would be appreciated! =INDEX(Codes!$A$2:$B$33,MATCH('F Codes'!G7,Codes!$A$2:$A$33,FALSE),2) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching numbers and text
Try:
=VLOOKUP('F Codes'!G7,Codes!$A$2:$B$332,0) "mndpy" wrote: What lookup should I try? "Bernard Liengme" wrote: Suggest you do a test to check if the numbers are actually text. In a cell in F Codes sheet enter =G7=Codes!A12 (replace A12 by a cell that has a number). Type the same number into G7 as you see in Codes!A12; you should get TRUE as the result. Or on Codes sheet in D2 (or wherever) use =ISNUMBER(A2) and copy down to D33 But why not use VLOOKUP? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mndpy" wrote in message ... I have on spreadsheet that has a column with a list of single numbers or letters the next column are times associated with those numbers and letters. I want to type in one of those numbers or letters and have it displayed in another worksheet the time associted with it. I have it half working that is displays the letters but is comes up with a #N/A when you type in a number to match. Here is what I am using. Any help would be appreciated! =INDEX(Codes!$A$2:$B$33,MATCH('F Codes'!G7,Codes!$A$2:$A$33,FALSE),2) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching numbers and text
That works but only for the letters I'm looking for. It still doesn't like
looking for the #'s. Any idea on how to search both? "Toppers" wrote: Try: =VLOOKUP('F Codes'!G7,Codes!$A$2:$B$332,0) "mndpy" wrote: What lookup should I try? "Bernard Liengme" wrote: Suggest you do a test to check if the numbers are actually text. In a cell in F Codes sheet enter =G7=Codes!A12 (replace A12 by a cell that has a number). Type the same number into G7 as you see in Codes!A12; you should get TRUE as the result. Or on Codes sheet in D2 (or wherever) use =ISNUMBER(A2) and copy down to D33 But why not use VLOOKUP? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mndpy" wrote in message ... I have on spreadsheet that has a column with a list of single numbers or letters the next column are times associated with those numbers and letters. I want to type in one of those numbers or letters and have it displayed in another worksheet the time associted with it. I have it half working that is displays the letters but is comes up with a #N/A when you type in a number to match. Here is what I am using. Any help would be appreciated! =INDEX(Codes!$A$2:$B$33,MATCH('F Codes'!G7,Codes!$A$2:$A$33,FALSE),2) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching numbers and text
It will work on numbers and text PROVIDED that they have the same format i.e
general or text. "mndpy" wrote: That works but only for the letters I'm looking for. It still doesn't like looking for the #'s. Any idea on how to search both? "Toppers" wrote: Try: =VLOOKUP('F Codes'!G7,Codes!$A$2:$B$332,0) "mndpy" wrote: What lookup should I try? "Bernard Liengme" wrote: Suggest you do a test to check if the numbers are actually text. In a cell in F Codes sheet enter =G7=Codes!A12 (replace A12 by a cell that has a number). Type the same number into G7 as you see in Codes!A12; you should get TRUE as the result. Or on Codes sheet in D2 (or wherever) use =ISNUMBER(A2) and copy down to D33 But why not use VLOOKUP? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mndpy" wrote in message ... I have on spreadsheet that has a column with a list of single numbers or letters the next column are times associated with those numbers and letters. I want to type in one of those numbers or letters and have it displayed in another worksheet the time associted with it. I have it half working that is displays the letters but is comes up with a #N/A when you type in a number to match. Here is what I am using. Any help would be appreciated! =INDEX(Codes!$A$2:$B$33,MATCH('F Codes'!G7,Codes!$A$2:$A$33,FALSE),2) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching numbers and text
maybe this give a trick on the match formula with index function.
the cell 'F Codes'!G7 maybe was formatted as text, try to change and use format as general on the said cell. Regards "mndpy" wrote: I have on spreadsheet that has a column with a list of single numbers or letters the next column are times associated with those numbers and letters. I want to type in one of those numbers or letters and have it displayed in another worksheet the time associted with it. I have it half working that is displays the letters but is comes up with a #N/A when you type in a number to match. Here is what I am using. Any help would be appreciated! =INDEX(Codes!$A$2:$B$33,MATCH('F Codes'!G7,Codes!$A$2:$A$33,FALSE),2) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching numbers and text
No such luck...any other ideas let me know. I appreciate it! Thanks!!!
"fair_thumb" wrote: maybe this give a trick on the match formula with index function. the cell 'F Codes'!G7 maybe was formatted as text, try to change and use format as general on the said cell. Regards "mndpy" wrote: I have on spreadsheet that has a column with a list of single numbers or letters the next column are times associated with those numbers and letters. I want to type in one of those numbers or letters and have it displayed in another worksheet the time associted with it. I have it half working that is displays the letters but is comes up with a #N/A when you type in a number to match. Here is what I am using. Any help would be appreciated! =INDEX(Codes!$A$2:$B$33,MATCH('F Codes'!G7,Codes!$A$2:$A$33,FALSE),2) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with Matching Text Fields - Then Moving the Matching Cells Side by Side | Excel Discussion (Misc queries) | |||
| Checks, Matching and deleting numbers | Excel Discussion (Misc queries) | |||
| vlookup with not exactly matching numbers | Excel Discussion (Misc queries) | |||
| matching a combination of numbers | Excel Discussion (Misc queries) | |||
| matching numbers in col a and col b | Excel Worksheet Functions | |||
Hybrid Mode
