VLOOKUP Problem

[dhx10000]

I can't figure this one out because it looks like everything is ok. I keep receiving an N/A error, all I'm trying to do is designate a number in a column that corresponds to a specific text value in another column. F2 contains my text that I am using to compare to the VLOOKUP range I created. My formula:

=VLOOKUP(F2,Status_ID,1,FALSE)

F2 = Reject: After Interview
Status_ID is a defined range that consists of this:

1 Submit: Pending Review
2 Request: On-site
3 Request: Phone Screen
4 Request: Questionnaire
5 Request: Make Offer
6 Accepted: Pending Questionnaire
7 Accepted: Pending Phone Screen

What am I doing wrong?

 

[Norie]

Forgive me if I'm wrong but I don't see the text Reject:After interview in the range Status_ID.

Also, why are you using a VLOOKUP to lookup the first column?

 

[dhx10000]

Sorry, Reject: After Interview is definitely there, it is number 16 on the list, I just didn't copy the entire list. I thought when you designate this number, which references the first column, it would tell VLOOKUP to give me that value, which is what I want.

 

[Norie]

What are you actually trying to do here?

What value do you actually want to return and what is the actual range you are looking up?

 

[dhx10000]

Here is my actual range:

1 Submit: Pending Review
2 Request: On-site
3 Request: Phone Screen
4 Request: Questionnaire
5 Request: Make Offer
6 Accepted: Pending Questionnaire
7 Accepted: Pending Phone Screen
8 Accepted: Pending On-site
9 Hire: Pending Offer Accepted
10 Hire: Pending Candidate Arrival
11 Hire: Closed
12 Reject: After Resume Review
13 Reject: After Questionnaire
14 Reject: After Phone Screen
15 Reject: After Interview
16 Reject: By Candidate
17 Reject: Offer
18 Reject: Counter-Offer
19 On-Hold


On another sheet, have a F:F that contains values such as, Reject: Offer, Reject: By Candidate, etc. I want to return the Number that corresponds to the value in my F:F cells.

So if cell F3 contains "Reject: Offer", I would like my cell with the VLOOKUP formula to return 17.

 

[Norie]

That isn't the way VLOOKUP works.

The value you want to lookup should be in the left most column, and the value to return to the right off that.

As you have it currently, as far as I can see, you are trying to lookup Reject : Offer, or whatever, against the numbers 1-19.

You could either change the layout of the data or use MATCH/INDEX.

 

[dhx10000]

Perfect, I just switched the arrangement of my range columns and it works. Thank you very much...I used VLOOKUP in the past and it worked, I didn't know the columns had to be arranged like this.

 

[steve case]

I copied and pasted your stuff, and it works fine. But if I format the first column in your named range "Status_ID" as text, it returns #N/A just like you said.
.
.
.
.
.

 

[barry houdini]

...or if your numbers are always 1, 2, 3 etc. then you could use

=MATCH(F3,B1:B17,0)

where B1:b17 contains your descriptions

 

[Aladin Akyurek]

Here is my actual range:

1 Submit: Pending Review
2 Request: On-site
3 Request: Phone Screen
4 Request: Questionnaire
5 Request: Make Offer
6 Accepted: Pending Questionnaire
7 Accepted: Pending Phone Screen
8 Accepted: Pending On-site
9 Hire: Pending Offer Accepted
10 Hire: Pending Candidate Arrival
11 Hire: Closed
12 Reject: After Resume Review
13 Reject: After Questionnaire
14 Reject: After Phone Screen
15 Reject: After Interview
16 Reject: By Candidate
17 Reject: Offer
18 Reject: Counter-Offer
19 On-Hold


On another sheet, have a F:F that contains values such as, Reject: Offer, Reject: By Candidate, etc. I want to return the Number that corresponds to the value in my F:F cells.

So if cell F3 contains "Reject: Offer", I would like my cell with the VLOOKUP formula to return 17.

If you sort your actual range:

=MATCH(F3,ActualRange,1)

Otherwise:

=MATCH(F3,ActualRange,0)