Trying to combine IF and VLOOKUP Functions

[Dezigrar]

Hello!
I'm using Windows 8, Excel 2013 for a class homework assignment, but the directions are unhelpful. I've combed through excel forums and videos for hours and have found many videos -related- to my problem, but none that have helped me really address and fix it.

I have two spreadsheets in excel. The first spreadsheet has a large data table. Pertinent columns on the data table are: The codes to distinguish colleges, which corresponds to a (CollCode) cell on the second sheet, and whether it's public or private (Type). The parentheses are the data range names I game them. In the Public/Private column, the colleges are either 1s or 2s. 1s are Public, 2s are Private.

On the second spreadsheet... I need to VLOOKUP whether the college is a 1 or a 2. I have =VLOOKUP(CollCode,Data,4) set up. This looks at what code I've put in (CollCode), matches it to the code on the first sheet in the Data table, looks to column four of the row with that code, and tells me the value.

Right now, with the code I have in, the resulting number is 1. The code I put in is of a college that is Public.
So.

I need to somehow, within the same cell, recognize that if the value is "1", then the value of the cell I'm working in should say "Yes", and if the value is something else (or 2, more specifically), then the answer is "No".



I thought this would look something like this: =IF(VLOOKUP(CollCode,Data,4)=1, Yes, No)
But I'm wrong. Any help? I need this cell to use another cell to tell it where it should look, take the resulting number, and pass judgement on it...

Thank you in advance!

 

[MarcelBeug]

=INDEX({"yes","no"},VLOOKUP(CollCode,Data,4,0))

Remark: your solution is fine as well if you add quotes around "yes" and "no".

 

[Dezigrar]

Thank you so much! I can't believe it was as simple as quotations... That's probably why I've been eating myself around in this cycle.

Would you by any chance know why my next equation isn't working? ((I'm not using other functions because of the homework requirement)).


=IF(VLOOKUP(CollCode,Data,3)=StudState,VLOOKUP(CollCode,Data,InState),VLOOKUP(CollCode,Data,OutState))

If (Use the code to search the data table for the corresponding College's State in column 3) = The student's State, then look up the in state tuition on that table for that college. If not, then look up the out of state tuition for that college.

This time, I'm trying to get numbers as the answers. One if the states match, a different number if the states are different.
I don't know if that makes sense...

 

[Goshgar]

Hello!
I'm using Windows 8, Excel 2013 for a class homework assignment, but the directions are unhelpful. I've combed through excel forums and videos for hours and have found many videos -related- to my problem, but none that have helped me really address and fix it.

I have two spreadsheets in excel. The first spreadsheet has a large data table. Pertinent columns on the data table are: The codes to distinguish colleges, which corresponds to a (CollCode) cell on the second sheet, and whether it's public or private (Type). The parentheses are the data range names I game them. In the Public/Private column, the colleges are either 1s or 2s. 1s are Public, 2s are Private.

On the second spreadsheet... I need to VLOOKUP whether the college is a 1 or a 2. I have =VLOOKUP(CollCode,Data,4) set up. This looks at what code I've put in (CollCode), matches it to the code on the first sheet in the Data table, looks to column four of the row with that code, and tells me the value.

Right now, with the code I have in, the resulting number is 1. The code I put in is of a college that is Public.
So.

I need to somehow, within the same cell, recognize that if the value is "1", then the value of the cell I'm working in should say "Yes", and if the value is something else (or 2, more specifically), then the answer is "No".



I thought this would look something like this: =IF(VLOOKUP(CollCode,Data,4)=1, Yes, No)
But I'm wrong. Any help? I need this cell to use another cell to tell it where it should look, take the resulting number, and pass judgement on it...

Thank you in advance!

try formula below
=IF(VLOOKUP(CollCode,Data,4,0)=1, "Yes", "No")