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.

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, 2023 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! Jan 5, 2023 at 12:40
  • Error? What about =VLOOKUP('index-to-lookup',ARRAYFORMULA({D:D,A:A}),2,0)?
    – JohnSUN
    Jan 5, 2023 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. Jan 5, 2023 at 12:50
  • Fortunately though, your idea to use INDEX and MATCH works perfectly and solves my problem. Thank you for your help! Jan 5, 2023 at 12:51

1 Answer 1

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 
) ) )
3
  • Wait, is it backslash that appends whole columns in GS arrays? Is therefore an arrayformula necessary? Jan 10, 2023 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. Jan 10, 2023 at 14:11
  • I gather that this is your first question at Stack Exchange. See What should I do when someone answers my question? Jan 10, 2023 at 14:11

Your Answer

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

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