Expected result (formula cells being bold):
| SHEET 1 | SHEET 2 | |||
|---|---|---|---|---|
| Key | Expected value | Key | Misc data | Expected value |
| 12 | C | 10 | asd | A |
| 14 | E | 11 | asd | B |
| 10 | A | 12 | asd | C |
| 13 | asd | D | ||
| 14 | asd | E |
The formula I'm trying to use is
=LOOKUP(A2,'Sheet2'!$A$2:$A,'Sheet2'!$C$2:$C)
When I use this on a single sheet (tab) then it works as intended. However as soon as I'm trying to get data from another sheet, it fails with:
Error Did not find value 'KEY' in LOOKUP evaluation.
The issue was with the key columns holding values with different data types (number vs plain text). After setting both to same data type, the formula started working.