I need to create a range off of two separate columns within a sheet.
Let's say column A is my index, while column D is third party index.
When using VLOOKUP i can lookup my index to find 3rd party index by using $A:$D range:
VLOOKUP('index-to-lookup'; 'my sheet'!$A:$D; 4; false)
However, I need to be able to lookup the other way around as well: knowing 3rd party index I want to find my index.
I've tried already:
VLOOKUP('index-to-lookup'; 'my sheet'!$A:$D; -1; false)
VLOOKUP('index-to-lookup'; 'my sheet'!$D:$A; 4; false)
VLOOKUP('index-to-lookup'; 'my sheet'!$D:$A; -1; false)
as well as using arrays:
VLOOKUP('index-to-lookup'; {'my sheet'!$A:$A, 'my sheet'!$D:$D}; -1; false)
combining single columns with importrange etc. but neither have worked so far.
There's no answer for similar problem to be founud.
=INDEX(A:A,MATCH('index-to-lookup',D:D,0))
or=VLOOKUP('index-to-lookup',{D:D,A:A},2,0)
=VLOOKUP('index-to-lookup',ARRAYFORMULA({D:D,A:A}),2,0)
?