looking up a value in not exactly the same row from another column

I have three columns A,B and C. I want to look up a value in say row 2 of A in column B (mind you the exact value in row 2 of column A may be within the range of values in column B and in a row different from row 2 in B. In most cases the values are found in say row 5 in B while in A it is in row 7 or further down) and when the match is found in B, I need to return value from row X in column that matches same row in column B. ie the values to be return in column C will be at same row where the match from column, was found in column B.
Something like "if A3=B1:B5,CX,0. such that the row CX= the row where the value in A3 is found in B1:B5.

Thanks in anticipation of a swift response.

Try putting the formula below in cell "C1":

Formula:

Please Login or Register  to view this content.

and then copy the formula down column "C"

thanks but it didn't work. What I meant was to look for a value in B that matches a row in C, such that my response will come from column A, in that the result supplied from column A, will be at same row as the row in column B that matches the value in C.

attached is d sample and sheet 2 contains the expected result. thanks

please all, can anyone assist with my query. I have attached a sample data and expected result.Thanks in anticipation of a favorable response.

Yeah it is. More or else I want to open up the gaps. Hope to read from you soon. Thanks.

the number on column A are random but in ascending order.

but the number for state and country are not random but they are also in ascending order.

please anyone with positive response? I desperately need this as am running on a tight schedule.

@ xer2 you are right.

Please any idea on how to go about it. I am running on a tight timeline banking on you guys.
Thanks.

I have more than 5 thousand entries. The problem is if I do it manually so where along the line I have to match it with a different set of data and repeat same process again. so doing it manually is not the smartest thing to do right now. Please if you can be of help let me know. I just have the feeling that you are stringing me along.

@Xer the info in column B will be values.

thanks will give you feedback later!

hello Xer2 am a novice please I tried running it in just the way you run normal excel command and I couldn't. Please if it is not some much of a trouble how do I run it?

Hello xer2 it works. But I noticed that as from 261 it matches 261 of column B to 262 of column c. I also observed that it looks at the last digit in column B and that of column C. below is an extract of the wrong match.

"B" "C"
. .
. .
289 298
290 300
291 301
292 302
293 303
294 304
295 305
296 306
297 307
298 308
309
300 310
301 311
mid-way while running it it give an error message of 1004. How do I correct this?