[beto44]

Hello, I have column a with numbers and column be with numbers in column c1 and d1 I have 2 numbers and each number needs to be searched for in a different column which can't be in ascending order and I need to determine which number was found first. The number in c1 looks for numbers in column A and the number in d1 looks for the numbers in column B
Depending on the result I need to post a number, if it's from one column let's say it will equal 1 and if it's from the other it would equal 2
Here es an example of the data:
Is there a way to come up with a formula for this? I tried the MATCH formul but sometimes it gives me the #N/A and can't do anything with the results.
Thanks

[JBeaucaire]

MATCH() is the correct function. Since MATCH() will return an error if the value has no match (at least it does when you're searching for exact matches), then you have to couple that with an IF(ISERROR() test.

So, for the first value in C1:
=IF(ISERROR(MATCH(C1, A:A, 0)), 0, MATCH(C1, A:A, 0))

For the value in D1
=IF(ISERROR(MATCH(D1, B:B, 0)), 0, MATCH(D1, B:B, 0))

Now, we compare these two values and if the first is less than the second, return 1, if not, then return 2.

=IF(IF(ISERROR(MATCH(C1,A:A, 0)), 0, MATCH(C1,A:A, 0)) < IF(ISERROR(MATCH(D1,B:B, 0)), 0,MATCH(D1,B:B, 0)), 1, 2)

BTW, if you're using Excel 2007, those formulas can shorten up quite a bit with the IFERROR() function. Let me know if you can use that.

[beto44]

Thanks for the super fast answer, I have one problem with this solution.
My columns can't be in ascending or decending order so with the MATCH() formula I have to use 0 as my match type, but sometimes on the column that I'm serching for a number the exact number gets skiped, on the sample that I sent I'm looking for 4.44 and you find it on A34, suppose that A34 was 4.45, how can I get that answer if the number is greater that or equal to 4.44 without arranging the columns with ascending order?
Thanks

[JBeaucaire]

The options you have are:

Use "0" for exact matches and the data can be random or sorted in any order you wish.

Use "1" for "near" matches but it rounds DOWN numerically in the list not up, and the data must be sorted ascending for it to work at all.

You are asking for a hybrid that doesn't exist as far as I know... any order, fuzzy match and round UP to the nearest value. Don't think I know of a way to do that.