# VLOOKUP Problem

#### dhx10000

##### Board Regular
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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?

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.

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?

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.

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.

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.

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.
.
.
.
.
.

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

=MATCH(F3,B1:B17,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)

Replies
8
Views
416
Replies
1
Views
197
Replies
1
Views
521
Replies
2
Views
538
Replies
0
Views
256

1,221,214
Messages
6,158,567
Members
451,500
Latest member
kwavic

### 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.

### Which adblocker are you using?

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

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