SOLVED

# Vlook up Error

Copper Contributor

# Vlook up Error

Hi Community,

I'm trying to do a vlookup to capture  the values from my second sheet( Values). This is the formula I used below. Ideally, I want the C column on the master sheet to read Rejected, Approved, Cancelled or Error

Steps taken
I selected cell A2( In the Master Sheet), Click the Values sheet and selected all the values, I clicked F4, Then 2, False

=VLOOKUP(A2,Values!\$A\$1:\$B\$4,2,FALSE)

5 Replies
best response confirmed by Testbot1 (Copper Contributor)
Solution

# Re: Vlook up Error

@Testbot1 The lookup values (column A on the Master sheet) are a mix of texts and numbers. Text "17" is not equal to number 17, hence #N/A#

# Re: Vlook up Error

First, you will need to reverse the order of the table being used for looking up those possible returns, so it begins with the lower numbers, and you want the bottom (lowest) number to be zero

 0 Out of range 3 Error 15 Cancelled 16 Approved 17 Rejected

Then, if the only numbers that are ever going to appear in column A are the ones in the table) your formula should work. Otherwise, for a range lookup, you would want the final word in that formula to be "True"

=VLOOKUP(A2,Values!\$A\$1:\$B\$4,2,TRUE). or, I always find it easier to use 1 or 0, as in

=VLOOKUP(A2,Values!\$A\$1:\$B\$4,2,1)

# Re: Vlook up Error

I missed that mix....

Thank you!

# Re: Vlook up Error

Thank you! This and the combination of response above solve the issue