I have 3 columns of interest. Column A is a list of numbers written out as strings. (seven, eleven, nineteen) Column B is a list of numbers as numbers. (7, 11,19) Cell C1 has the formula: =LOOKUP("seven",A1:A3,B1:B3) this formula always returns 19 if there is ANYTHING in A3 If there is nothing in A3, it returns 11, and only if there is nothing in A2, A3 will it return 7. enter image description here What am I misunderstanding? Thanks
I was expecting the test to find the matching string and yield the number in the range but it always skips to the last value by default.
If I put a string in A3 of "cow" it yields 19 enter image description here if I put a string in A3 of "three" it yields 11 enter image description here
I've also tried VLOOKUP and HLOOKUP with worse results. I tried with and without "$" in ranges.
What kind of logic is this? lol
As per LOOKUP() documentation:
LOOKUP(search_key, search_range|search_result_array, [result_range])
if data in search_range or search_result_array is sorted. Use VLOOKUP, HLOOKUP, or other
related functions if data is not sorted.You may try this for your test dataset:
=xlookup("seven",A:A,B:B,)