vlookup for unsorted data does not work

Question

i have a table as following, i want to use vlookup but it does not work.

the format cell of column b and c is text. i wany to find the text in column B from column C. my formula is:

=VLOOKUP(F5,B:C,1,FALSE)
f5=مركز بهداشت دانشگاه تهران

but it returns: #n/a when i want to find column C from column B, it works well. please help me. what should i do? is there any mismatch with arabic?

Answer 1

K_B's explanation of why your formula doesn't work is correct, you can't use VLookup to search column C and return from column B

You can use INDEX and MATCH instead, like this

=INDEX(B:B,MATCH(F5,C:C,0))

Answer 2

The formula requires the text to be found to be in the 1st column from the second argument (B:C in your case).

Then it will return the value from the ith column to you where i is the 3rd argument of the formula (1 in your case).

This will only work 1 way (Search in B, return from C) and never the other way (search in C return from B).

If you require to work from the other way around you will have to either put the columns in the opposite order OR fill column D with =B1 etc... and use your formula on C:D.

Now whenever the searched text isnt present in your first column the result will always be #n/a

Answer 3

I'm not an Arabic speaker, but based on what you are saying, you have a value in cell F5 which you want to lookup in column B and return the corresponding value from column C. Assuming that is correct, then your formula should read

=vlookup(F5, B:C, 2, FALSE)

However, that would not cause #n/a to be returned. It does not look to me like the value given for F5 exists in column B in your example data - can you confirm?