3

I'm trying to edit a sheet and use vlookup to get a text from another tab but I'm getting an #N/A error.

What I would like to achieve is to get "March 1st" of the notes tab to G65 of the contacts tab. However, the data in the notes tab from columns A to I is a query from the contacts tab while the "type your notes here" column is not.

Contacts tab

Notes tab

Formula bar shows correct value

player0
  • 124,011
  • 12
  • 67
  • 124
Jasper
  • 33
  • 1
  • 5
  • Surprisingly, the formula bar picks up what I was trying to get but the cell shows #N/A. – Jasper Feb 26 '19 at 15:14
  • I'm still getting #N/A on the cell but the formula bar gets it correctly. Here's the copy of the sheet. https://docs.google.com/spreadsheets/d/1VZxGPWWfu_V92B7r87BkfQY1jomYwi0IDo03wzRB7-U/edit?usp=sharing – Jasper Feb 26 '19 at 15:33

1 Answers1

4

referencing a reference which gets referenced kinda creates a paradox so this route is not possible. therefore try: 

=ArrayFormula(IFERROR(VLOOKUP(B7:B17, 
 {query(Contacts!A7:I,"Select B Where F is not null order by F"),
  query('Notes (View Only)'!J3:J, "where J is not null")}, 2, 0)))

1st column of 2nd VLOOKUP argument must contain the same stuff that 1st argument of VLOOKUP in your case it's not so you will need to switch them...

=VLOOKUP(B65, {'Notes (View Only)'!B3:B9, 'Notes (View Only)'!A3:J9}, 1, 0)

and if you want also the 9th column use:

=VLOOKUP(B65, {'Notes (View Only)'!B3:B9, 'Notes (View Only)'!A3:J9}, {1, 10}, 0)

or maybe you just need:

=VLOOKUP(B65, 'Notes (View Only)'!B3:J9, 9, 0)
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Dropping (notes tab) column G from the query function: =query(Contacts!A7:I,"Select A,B,C,D,E,F Where F is not null order by F") And, using this formula (contacts tab) resolved this question: =ArrayFormula(IFERROR(VLOOKUP(B7,{query(Contacts!A7:I,"Select B Where F is not null order by F"),query('Notes (View Only)'!J3:J, "where J is not null")}, 2,0))) Thanks! – Jasper Feb 26 '19 at 16:39