Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a match and adding to the statement
I'm using the formula:
=IF(MATCH(C51,'Data Sheet'!A4:A103,1),"Added Holiday(s) For:","") What I'm wanting to do is to add the information from 'Data Sheet'!B4:B103 to print out after the "Added" statement (the actual name of the holiday listed). What would be the best way to accomplish this?....Thanks....Ben |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a match and adding to the statement
Hi!
Try this: =IF(ISNA(MATCH(C51,'Data Sheet'!A4:A103,1)),"","Added Holiday(s) For: "&VLOOKUP(C51,'Data Sheet'!A4:B103,2)) Biff "ben simpson" wrote in message ... I'm using the formula: =IF(MATCH(C51,'Data Sheet'!A4:A103,1),"Added Holiday(s) For:","") What I'm wanting to do is to add the information from 'Data Sheet'!B4:B103 to print out after the "Added" statement (the actual name of the holiday listed). What would be the best way to accomplish this?....Thanks....Ben |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a match and adding to the statement
Hi there. Thanks for looking in. I tried the formula and realized that
there might be as many as 4 matches. This only listed the last match. How can i make it list all of them?.....Ben "Biff" wrote: Hi! Try this: =IF(ISNA(MATCH(C51,'Data Sheet'!A4:A103,1)),"","Added Holiday(s) For: "&VLOOKUP(C51,'Data Sheet'!A4:B103,2)) Biff "ben simpson" wrote in message ... I'm using the formula: =IF(MATCH(C51,'Data Sheet'!A4:A103,1),"Added Holiday(s) For:","") What I'm wanting to do is to add the information from 'Data Sheet'!B4:B103 to print out after the "Added" statement (the actual name of the holiday listed). What would be the best way to accomplish this?....Thanks....Ben |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a match and adding to the statement
Hi there. Thanks for looking in. I tried the formula and realized that
there might be as many as 4 matches. This only listed the last match. How can i make it list all of them?.....Ben Ugh! I see you're using the match_type 1 that means your data is sorted ascending meaning that if an exact match isn't found then find the closest match. So, would these other matches be exact matches or the closest matches? If you're going for the closest match this will be very complicated and the resultant formula will be very long. Any chance I can see your file? Biff "ben simpson" wrote in message ... Hi there. Thanks for looking in. I tried the formula and realized that there might be as many as 4 matches. This only listed the last match. How can i make it list all of them?.....Ben "Biff" wrote: Hi! Try this: =IF(ISNA(MATCH(C51,'Data Sheet'!A4:A103,1)),"","Added Holiday(s) For: "&VLOOKUP(C51,'Data Sheet'!A4:B103,2)) Biff "ben simpson" wrote in message ... I'm using the formula: =IF(MATCH(C51,'Data Sheet'!A4:A103,1),"Added Holiday(s) For:","") What I'm wanting to do is to add the information from 'Data Sheet'!B4:B103 to print out after the "Added" statement (the actual name of the holiday listed). What would be the best way to accomplish this?....Thanks....Ben |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a match and adding to the statement
Well...oops...its supposed to be a 0 match. It will be an exact match. I'm
matching a list of dates (holidays that I've looked up manually) against todays date when it falls within a work cycle. The message needs to remain there thru the end of the cycle (which I can easily reference). I broke it down into 2 parts (Keeping the "Added" statement seperate, in in the preceeding cell). It now looks like this: In A28 =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"","Added Holiday(s) For:") In C28 =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,0)) The F51:AF52 is the range of dates that I need to compare the list against. All of this of course lists only the last match it finds, while needing all the matches it finds listed. Unfortuneately I can't send it for you to look at. I'm locked out, and can't get permission to do so. Thanks again.....Ben "Biff" wrote: Hi there. Thanks for looking in. I tried the formula and realized that there might be as many as 4 matches. This only listed the last match. How can i make it list all of them?.....Ben Ugh! I see you're using the match_type 1 that means your data is sorted ascending meaning that if an exact match isn't found then find the closest match. So, would these other matches be exact matches or the closest matches? If you're going for the closest match this will be very complicated and the resultant formula will be very long. Any chance I can see your file? Biff "ben simpson" wrote in message ... Hi there. Thanks for looking in. I tried the formula and realized that there might be as many as 4 matches. This only listed the last match. How can i make it list all of them?.....Ben "Biff" wrote: Hi! Try this: =IF(ISNA(MATCH(C51,'Data Sheet'!A4:A103,1)),"","Added Holiday(s) For: "&VLOOKUP(C51,'Data Sheet'!A4:B103,2)) Biff "ben simpson" wrote in message ... I'm using the formula: =IF(MATCH(C51,'Data Sheet'!A4:A103,1),"Added Holiday(s) For:","") What I'm wanting to do is to add the information from 'Data Sheet'!B4:B103 to print out after the "Added" statement (the actual name of the holiday listed). What would be the best way to accomplish this?....Thanks....Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing data, then adding data to the new spreadsheet.. a conund | Excel Discussion (Misc queries) | |||
MATCH function | Excel Discussion (Misc queries) | |||
adding summed cells in a conditional sumif | Excel Worksheet Functions | |||
adding cells that contain formulas that have returned error messag | Excel Worksheet Functions |