Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
I would like to use an if statement to fill in data in one column - I have 1
lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
Hi!
Try this: =IF(S222=13,VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(V222,'RES TYPE'!E$2:$F$220,2,FALSE)) Biff "suzyque" wrote in message ... I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
I didn't try it, but it looks ok to me except that it would appear to do the
opposite of what you stated. if S222 = 13 then it will use VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE) The other thing is they are using different columns for the offsets (6 and 2), but that may be what you intended. Other than that, how is it not working? Ah, the 6 would be a problem because you are only looking in columns e:f. Either expand your lookup range or change the 6 to 2. -- Kevin Vaughn "suzyque" wrote: I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
Thanks this works but now if the cell doesn't =13 I am getting #N/A - any
suggestions - doesn't seem to be going to the other lookup "Kevin Vaughn" wrote: I didn't try it, but it looks ok to me except that it would appear to do the opposite of what you stated. if S222 = 13 then it will use VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE) The other thing is they are using different columns for the offsets (6 and 2), but that may be what you intended. Other than that, how is it not working? Ah, the 6 would be a problem because you are only looking in columns e:f. Either expand your lookup range or change the 6 to 2. -- Kevin Vaughn "suzyque" wrote: I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
Thanks this works but now if the cell doesn't =13 I am getting #N/A - any
suggestions? - doesn't seem to be going to the other lookup "Biff" wrote: Hi! Try this: =IF(S222=13,VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(V222,'RES TYPE'!E$2:$F$220,2,FALSE)) Biff "suzyque" wrote in message ... I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
Well, it is doing the other vlookup but it is not finding the lookup value in
V222 (that is what #N/A means. The value being looked up was not found. Assuming you are using this from Biff's reply =IF(S222=13,VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(V222,'RES TYPE'!E$2:$F$220,2,FALSE)) Check that the value you are entering in V222 (that is not 13) is in the range 'Restype'!E$2:$f220. If it appears that it is there, check it closer. There may be a space that you can't see that is causing it to be different than the value in V222. -- Kevin Vaughn "suzyque" wrote: Thanks this works but now if the cell doesn't =13 I am getting #N/A - any suggestions - doesn't seem to be going to the other lookup "Kevin Vaughn" wrote: I didn't try it, but it looks ok to me except that it would appear to do the opposite of what you stated. if S222 = 13 then it will use VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE) The other thing is they are using different columns for the offsets (6 and 2), but that may be what you intended. Other than that, how is it not working? Ah, the 6 would be a problem because you are only looking in columns e:f. Either expand your lookup range or change the 6 to 2. -- Kevin Vaughn "suzyque" wrote: I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
Slight change to this. The value being looked up was not in the range
E2:E220 (I said f220 previously, but it only checks the first column. In this instance it returns what's in column F.) -- Kevin Vaughn "Kevin Vaughn" wrote: Well, it is doing the other vlookup but it is not finding the lookup value in V222 (that is what #N/A means. The value being looked up was not found. Assuming you are using this from Biff's reply =IF(S222=13,VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(V222,'RES TYPE'!E$2:$F$220,2,FALSE)) Check that the value you are entering in V222 (that is not 13) is in the range 'Restype'!E$2:$f220. If it appears that it is there, check it closer. There may be a space that you can't see that is causing it to be different than the value in V222. -- Kevin Vaughn "suzyque" wrote: Thanks this works but now if the cell doesn't =13 I am getting #N/A - any suggestions - doesn't seem to be going to the other lookup "Kevin Vaughn" wrote: I didn't try it, but it looks ok to me except that it would appear to do the opposite of what you stated. if S222 = 13 then it will use VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE) The other thing is they are using different columns for the offsets (6 and 2), but that may be what you intended. Other than that, how is it not working? Ah, the 6 would be a problem because you are only looking in columns e:f. Either expand your lookup range or change the 6 to 2. -- Kevin Vaughn "suzyque" wrote: I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
Thanks Kevin - does it make a difference if the data in S222 or V222 is the
result of a =right command - could that be throwing it off? Thanks for your time "Kevin Vaughn" wrote: Slight change to this. The value being looked up was not in the range E2:E220 (I said f220 previously, but it only checks the first column. In this instance it returns what's in column F.) -- Kevin Vaughn "Kevin Vaughn" wrote: Well, it is doing the other vlookup but it is not finding the lookup value in V222 (that is what #N/A means. The value being looked up was not found. Assuming you are using this from Biff's reply =IF(S222=13,VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(V222,'RES TYPE'!E$2:$F$220,2,FALSE)) Check that the value you are entering in V222 (that is not 13) is in the range 'Restype'!E$2:$f220. If it appears that it is there, check it closer. There may be a space that you can't see that is causing it to be different than the value in V222. -- Kevin Vaughn "suzyque" wrote: Thanks this works but now if the cell doesn't =13 I am getting #N/A - any suggestions - doesn't seem to be going to the other lookup "Kevin Vaughn" wrote: I didn't try it, but it looks ok to me except that it would appear to do the opposite of what you stated. if S222 = 13 then it will use VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE) The other thing is they are using different columns for the offsets (6 and 2), but that may be what you intended. Other than that, how is it not working? Ah, the 6 would be a problem because you are only looking in columns e:f. Either expand your lookup range or change the 6 to 2. -- Kevin Vaughn "suzyque" wrote: I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
You might have to put VALUE( ) around your RIGHT formula to convert it
to a number, and as Kevin has pointed out the widths of your ranges are inconsistent with the column number you are trying to return data from. Hope this helps. Pete |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
I just noticed the difference in the formulas using either S222 or V222 (when
you mentioned it.) Ok, so if I understand correctly, S222 is not 13 so it is doing the vlookup on V222 ... and V222 is the result of a right function. And it might start out life as a number. Let me check something. Ok, then using the Right function will make V222 a string. And a vlookup on a string against a number will return #N/A, so, try using the value of V222 instead. Might need to wrap S222 in the value function also =IF(S222=13,VLOOKUP(value(S222),'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(value(V222),'RES TYPE'!E$2:$F$220,2,FALSE)) HTH -- Kevin Vaughn "suzyque" wrote: Thanks Kevin - does it make a difference if the data in S222 or V222 is the result of a =right command - could that be throwing it off? Thanks for your time "Kevin Vaughn" wrote: Slight change to this. The value being looked up was not in the range E2:E220 (I said f220 previously, but it only checks the first column. In this instance it returns what's in column F.) -- Kevin Vaughn "Kevin Vaughn" wrote: Well, it is doing the other vlookup but it is not finding the lookup value in V222 (that is what #N/A means. The value being looked up was not found. Assuming you are using this from Biff's reply =IF(S222=13,VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(V222,'RES TYPE'!E$2:$F$220,2,FALSE)) Check that the value you are entering in V222 (that is not 13) is in the range 'Restype'!E$2:$f220. If it appears that it is there, check it closer. There may be a space that you can't see that is causing it to be different than the value in V222. -- Kevin Vaughn "suzyque" wrote: Thanks this works but now if the cell doesn't =13 I am getting #N/A - any suggestions - doesn't seem to be going to the other lookup "Kevin Vaughn" wrote: I didn't try it, but it looks ok to me except that it would appear to do the opposite of what you stated. if S222 = 13 then it will use VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE) The other thing is they are using different columns for the offsets (6 and 2), but that may be what you intended. Other than that, how is it not working? Ah, the 6 would be a problem because you are only looking in columns e:f. Either expand your lookup range or change the 6 to 2. -- Kevin Vaughn "suzyque" wrote: I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
Thanks so much - I will have to wait till Monday to give this a try and will
let you know I appreciate your help (Pete & Biff too!) "Kevin Vaughn" wrote: I just noticed the difference in the formulas using either S222 or V222 (when you mentioned it.) Ok, so if I understand correctly, S222 is not 13 so it is doing the vlookup on V222 ... and V222 is the result of a right function. And it might start out life as a number. Let me check something. Ok, then using the Right function will make V222 a string. And a vlookup on a string against a number will return #N/A, so, try using the value of V222 instead. Might need to wrap S222 in the value function also =IF(S222=13,VLOOKUP(value(S222),'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(value(V222),'RES TYPE'!E$2:$F$220,2,FALSE)) HTH -- Kevin Vaughn "suzyque" wrote: Thanks Kevin - does it make a difference if the data in S222 or V222 is the result of a =right command - could that be throwing it off? Thanks for your time "Kevin Vaughn" wrote: Slight change to this. The value being looked up was not in the range E2:E220 (I said f220 previously, but it only checks the first column. In this instance it returns what's in column F.) -- Kevin Vaughn "Kevin Vaughn" wrote: Well, it is doing the other vlookup but it is not finding the lookup value in V222 (that is what #N/A means. The value being looked up was not found. Assuming you are using this from Biff's reply =IF(S222=13,VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(V222,'RES TYPE'!E$2:$F$220,2,FALSE)) Check that the value you are entering in V222 (that is not 13) is in the range 'Restype'!E$2:$f220. If it appears that it is there, check it closer. There may be a space that you can't see that is causing it to be different than the value in V222. -- Kevin Vaughn "suzyque" wrote: Thanks this works but now if the cell doesn't =13 I am getting #N/A - any suggestions - doesn't seem to be going to the other lookup "Kevin Vaughn" wrote: I didn't try it, but it looks ok to me except that it would appear to do the opposite of what you stated. if S222 = 13 then it will use VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE) The other thing is they are using different columns for the offsets (6 and 2), but that may be what you intended. Other than that, how is it not working? Ah, the 6 would be a problem because you are only looking in columns e:f. Either expand your lookup range or change the 6 to 2. -- Kevin Vaughn "suzyque" wrote: I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
Hi Kevin - I used your suggestion - actually I am learning so much - thanks -
I got it to work using the value function =IF(VALUE(S222)=13,VLOOKUP(V222,'res type'!E$2:$F$220,2,FALSE),VLOOKUP(S222,'res type'!A$2:$D$220,4,FALSE)) it worked for everything, however, I have cells in the s222 column that are not converting because they read 10A or 53A and 013 is being picked up as 13 - I formatted the column as text and thought that would work but it didn't matter. I worked around it by manyally converitng those cells - I appreciate your help - if you have any suggestions that would be great - but as for now - I am functional "Kevin Vaughn" wrote: I just noticed the difference in the formulas using either S222 or V222 (when you mentioned it.) Ok, so if I understand correctly, S222 is not 13 so it is doing the vlookup on V222 ... and V222 is the result of a right function. And it might start out life as a number. Let me check something. Ok, then using the Right function will make V222 a string. And a vlookup on a string against a number will return #N/A, so, try using the value of V222 instead. Might need to wrap S222 in the value function also =IF(S222=13,VLOOKUP(value(S222),'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(value(V222),'RES TYPE'!E$2:$F$220,2,FALSE)) HTH -- Kevin Vaughn "suzyque" wrote: Thanks Kevin - does it make a difference if the data in S222 or V222 is the result of a =right command - could that be throwing it off? Thanks for your time "Kevin Vaughn" wrote: Slight change to this. The value being looked up was not in the range E2:E220 (I said f220 previously, but it only checks the first column. In this instance it returns what's in column F.) -- Kevin Vaughn "Kevin Vaughn" wrote: Well, it is doing the other vlookup but it is not finding the lookup value in V222 (that is what #N/A means. The value being looked up was not found. Assuming you are using this from Biff's reply =IF(S222=13,VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(V222,'RES TYPE'!E$2:$F$220,2,FALSE)) Check that the value you are entering in V222 (that is not 13) is in the range 'Restype'!E$2:$f220. If it appears that it is there, check it closer. There may be a space that you can't see that is causing it to be different than the value in V222. -- Kevin Vaughn "suzyque" wrote: Thanks this works but now if the cell doesn't =13 I am getting #N/A - any suggestions - doesn't seem to be going to the other lookup "Kevin Vaughn" wrote: I didn't try it, but it looks ok to me except that it would appear to do the opposite of what you stated. if S222 = 13 then it will use VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE) The other thing is they are using different columns for the offsets (6 and 2), but that may be what you intended. Other than that, how is it not working? Ah, the 6 would be a problem because you are only looking in columns e:f. Either expand your lookup range or change the 6 to 2. -- Kevin Vaughn "suzyque" wrote: I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I use =if with vlookup statements
Glad you got it working. No, I can't think of any more suggestions at this
time. -- Kevin Vaughn "suzyque" wrote: Hi Kevin - I used your suggestion - actually I am learning so much - thanks - I got it to work using the value function =IF(VALUE(S222)=13,VLOOKUP(V222,'res type'!E$2:$F$220,2,FALSE),VLOOKUP(S222,'res type'!A$2:$D$220,4,FALSE)) it worked for everything, however, I have cells in the s222 column that are not converting because they read 10A or 53A and 013 is being picked up as 13 - I formatted the column as text and thought that would work but it didn't matter. I worked around it by manyally converitng those cells - I appreciate your help - if you have any suggestions that would be great - but as for now - I am functional "Kevin Vaughn" wrote: I just noticed the difference in the formulas using either S222 or V222 (when you mentioned it.) Ok, so if I understand correctly, S222 is not 13 so it is doing the vlookup on V222 ... and V222 is the result of a right function. And it might start out life as a number. Let me check something. Ok, then using the Right function will make V222 a string. And a vlookup on a string against a number will return #N/A, so, try using the value of V222 instead. Might need to wrap S222 in the value function also =IF(S222=13,VLOOKUP(value(S222),'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(value(V222),'RES TYPE'!E$2:$F$220,2,FALSE)) HTH -- Kevin Vaughn "suzyque" wrote: Thanks Kevin - does it make a difference if the data in S222 or V222 is the result of a =right command - could that be throwing it off? Thanks for your time "Kevin Vaughn" wrote: Slight change to this. The value being looked up was not in the range E2:E220 (I said f220 previously, but it only checks the first column. In this instance it returns what's in column F.) -- Kevin Vaughn "Kevin Vaughn" wrote: Well, it is doing the other vlookup but it is not finding the lookup value in V222 (that is what #N/A means. The value being looked up was not found. Assuming you are using this from Biff's reply =IF(S222=13,VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE),VLOOKUP(V222,'RES TYPE'!E$2:$F$220,2,FALSE)) Check that the value you are entering in V222 (that is not 13) is in the range 'Restype'!E$2:$f220. If it appears that it is there, check it closer. There may be a space that you can't see that is causing it to be different than the value in V222. -- Kevin Vaughn "suzyque" wrote: Thanks this works but now if the cell doesn't =13 I am getting #N/A - any suggestions - doesn't seem to be going to the other lookup "Kevin Vaughn" wrote: I didn't try it, but it looks ok to me except that it would appear to do the opposite of what you stated. if S222 = 13 then it will use VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE) The other thing is they are using different columns for the offsets (6 and 2), but that may be what you intended. Other than that, how is it not working? Ah, the 6 would be a problem because you are only looking in columns e:f. Either expand your lookup range or change the 6 to 2. -- Kevin Vaughn "suzyque" wrote: I would like to use an if statement to fill in data in one column - I have 1 lookup table with 6 columns and a spreadsheet with multiple columns - basically I want to ask if a cell=13 go to my look up table"res type" A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220 this is what I wrote but it is not working: =IF(S222=13,VLOOKUP(V222,'RES TYPE'!E$2:$F$220,6,FALSE),VLOOKUP(S222,'RES TYPE'!A$2:$B$220,2,FALSE)) I could really use some help - thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup and if statements | New Users to Excel | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
VLOOKUP + IF STATEMENTS | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
VLOOKUP statements | Excel Discussion (Misc queries) |