VLOOKUP Problem

dhx10000

Board Regular
Joined
Jul 11, 2005
Messages
144
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?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
.
.
.
.
.
 
Upvote 0
...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
 
Upvote 0
dhx10000 said:
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)
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top