Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is this possible ?
Is this possible?
I want to compare a number against 3 ranges and display a comment for the range it fits into. Example. Let's say the number is 6 <4 - would display no comment between 5 and 15 - would display the comments "standard" 15 would display no comment. This is only one line of comparison. Moving down the worksheet the next example might look like this. Let's say the number is 147 <80 - would display no comment between 81 and 125 - would display no comment 125 would display the comment "below standard" I am very new at vlookup and no nothing of nested functions. Any help would be graciously accepted. TIA |
#2
|
|||
|
|||
Hi
i think you can use VLOOKUP for this, depending on how your workbook is actually set up if you have on Sheet 2 ...........A..................B 1.......Number......Comment 2........4................<blank 3.........5...............Standard 4........15..............<blank 5.......125...........Below Standard then if you enter the number you want to return the comment for in cell A1 on Sheet 1 the formula would be =VLOOKUP(A1,Sheet2!$A$2:$B$5,2) which says, find the value in A1 in the first column of the table in the range Sheet2!A2:B5 and return the associated value from the 2nd column. hope this helps -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Genie Bohn" wrote in message .. . Is this possible? I want to compare a number against 3 ranges and display a comment for the range it fits into. Example. Let's say the number is 6 <4 - would display no comment between 5 and 15 - would display the comments "standard" 15 would display no comment. This is only one line of comparison. Moving down the worksheet the next example might look like this. Let's say the number is 147 <80 - would display no comment between 81 and 125 - would display no comment 125 would display the comment "below standard" I am very new at vlookup and no nothing of nested functions. Any help would be graciously accepted. TIA |
#3
|
|||
|
|||
You're on the right track with Vlookup, you will probably need to set up a
few reerence tables by the sounds of things, but once you're done it's fairly easy from there. Check out the Excel pages at www.nwarwick.co.uk for a simple tutorial on Vlookup, this should get you started. HTH Neil "Genie Bohn" wrote: Is this possible? I want to compare a number against 3 ranges and display a comment for the range it fits into. Example. Let's say the number is 6 <4 - would display no comment between 5 and 15 - would display the comments "standard" 15 would display no comment. This is only one line of comparison. Moving down the worksheet the next example might look like this. Let's say the number is 147 <80 - would display no comment between 81 and 125 - would display no comment 125 would display the comment "below standard" I am very new at vlookup and no nothing of nested functions. Any help would be graciously accepted. TIA |
#4
|
|||
|
|||
Set up three ranges, named FirstRange, SecondRange, and ThirdRange, then put
your value to be "compared" in A1 and put this formula in B1......... =IF(AND(ISNA(VLOOKUP(A1,FirstRange,2,FALSE)),ISNA( VLOOKUP(A1,SecondRange,2,F ALSE)),ISNA(VLOOKUP(A1,ThirdRange,2,FALSE))),"Not in Ranges",IF(AND(ISNA(VLOOKUP(A1,FirstRange,2,FALSE) ),ISNA(VLOOKUP(A1,SecondRa nge,2,FALSE))),VLOOKUP(A1,ThirdRange,2,FALSE),IF(I SNA(VLOOKUP(A1,FirstRange, 2,FALSE)),VLOOKUP(A1,SecondRange,2,FALSE),VLOOKUP( A1,FirstRange,2,FALSE)))) Note, all on one line, watch out for email word-wrap......... Vaya con Dios, Chuck, CABGx3 "Genie Bohn" wrote in message .. . Is this possible? I want to compare a number against 3 ranges and display a comment for the range it fits into. Example. Let's say the number is 6 <4 - would display no comment between 5 and 15 - would display the comments "standard" 15 would display no comment. This is only one line of comparison. Moving down the worksheet the next example might look like this. Let's say the number is 147 <80 - would display no comment between 81 and 125 - would display no comment 125 would display the comment "below standard" I am very new at vlookup and no nothing of nested functions. Any help would be graciously accepted. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|