Please look at the Tables 1.X and Tables 2.X in the attachment.

The formulas in Tables X.1 are absolutely identical and created via copy & past by formula&format. The surprise is that the LOOKUP command in Table 1.1 cannot locate the correct (sR/s0) coordinate from Table 1.0 in the first column of the table while in any other columns in Table 1.1 or Table 2.1 the same command is OK.

Does someone know what is the reason for LOOKUP to behave differently in these two examples. I did various of test and I did not find the reason.



Thank you very much in advance

Attached Files

Test LOOKUP.xlsm (30.8 KB, 10 views)

The search vector for LOOKUP needs to be sorted ascending.
Try =INDEX($B$5:$B$13;MATCH(K6;C5:C13;0)) instead
Why it works in the second table I don't have time to search for now. Will get at it later

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I also did it using INDEX and MATCH however, my question was why LOOKUP works fine with Table 2.x but does not work with Table 1.x
The trends of the data in both tables are the same.

Please not that MATCH work with option 0 for Table 1.x It does not work with option 1. However MATCH with option 1 works for Table 2.x regardless that the data are not sorted. Something similar happened with LOOKUP.

1.The LOOKUP vector ( in your case col C) must be placed in ascending order otherwise the results might be wrong ( see https://support.microsoft.com/kb/324986)
2. The MATCH function works perfectly with option 0 (see attached)

Attached Files

Test LOOKUP.xlsm (30.1 KB, 9 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Again, I would like to stress again my question: Why LOOKUP works for Tables 2.x but it does not work for Tables 1.x. In both tables the values in Column C are not in ascending order. The trend in the values is the same in both tables: increasing until certain value and then decreasing.