1

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.

Improve this question
8
  • Welcome! vlookup vs index match - =INDEX(A:A,MATCH('index-to-lookup',D:D,0)) or =VLOOKUP('index-to-lookup',{D:D,A:A},2,0)
    – JohnSUN
    Jan 5 at 12:39
  • Unfortunately, the second option to combine 2 columns into an array, results in an error. I'll try INDEX MATCH though. Thank you!
    – Kuba Stachu
    Jan 5 at 12:40
  • Error? What about =VLOOKUP('index-to-lookup',ARRAYFORMULA({D:D,A:A}),2,0)?
    – JohnSUN
    Jan 5 at 12:42
  • This also results in an error, seems like it's impossible to combine existing columns with just a comma "," into an array. There's probably a function for that, that appends each row, but none that I could find.
    – Kuba Stachu
    Jan 5 at 12:50
  • Fortunately though, your idea to use INDEX and MATCH works perfectly and solves my problem. Thank you for your help!
    – Kuba Stachu
    Jan 5 at 12:51

1 Answer 1

Reset to default
0

With keys to look up in A2:A, you can find them in 'my sheet'!D2:D, returning the values in 'my sheet'!A2:A, like this:

=arrayformula( iferror( vlookup( 
  A2:A; 
  { 'my sheet'!D2:D \ 'my sheet'!A2:A }; 
  2; 
  false 
) ) )
Improve this answer
3
  • Wait, is it backslash that appends whole columns in GS arrays? Is therefore an arrayformula necessary?
    – Kuba Stachu
    Jan 10 at 13:36
  • The arrayformula() wrapper lets vlookup() process all the search keys in the column A2:A in one go. You can leave it out if you want to process those search keys one by one. In the event your spreadsheet locale uses commas , as decimal separators, you will have to use semicolons ; as formula argument separators and backslashes '\' as { array expression } horizontal separators.
    – doubleunary
    Jan 10 at 14:11
  • I gather that this is your first question at Stack Exchange. See What should I do when someone answers my question?
    – doubleunary
    Jan 10 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.