0

See this example spreadsheet.

I get #N/A value when I try to find bbb on sheet2 using formula =VLOOKUP(A1;sheet2!$A$1:$B$4;1;FALSE)

How to fix it?

  • While including a demo spreadsheet is helpful, questions should be self contained. In other words, add the formula an a sample of the data to the question itself.. – Rubén Feb 1 at 2:16
  • Thanks. Added the formula to the question. – klor Feb 1 at 6:09
1

VLOOKUP is able to look for something only if that something is in the first column of the lookup. In your case is bbb in the second column of the range A1:B4 you looking for. Fix is:

  • switch A column with B column on your sheet2
  • exclude A column from range: =VLOOKUP(A1; sheet2!B1:B; 1; 0)
  • use: =VLOOKUP(A1; {sheet2!B1:B\sheet2!A1:B}; 1; 0)
  • In my real sheet I can not switch the order of the columns, and I have multiple columns in the table between the source and target columns. Is there any solution for this? – klor Jan 30 at 12:30
  • @klor yes. check the 3rd fix... with it you can virtually move the B column to be the first column – user0 Jan 30 at 12:33
  • It seems to work. See demo table. Could you explain deeply in the answer how to move a column virtually? What is the syntax rule? Thanks! – klor Jan 30 at 12:57
  • @klor I can try... your actual data are in a table in sheet2!A1:B - let's call this table "the real one" because you have 2 columns (A and B) and you refer to this with range sheet2!**A1:B**. however, in the 3rd fix, we need to use {sheet2!B1:B\sheet2!A1:B} as a range instead of sheet2!A1:B eg. with these array brackets {} we can construct virtual table where 1st column is B 2nd column is A and 3rd column is B. its like we virtually appended B column to real table just for purpose to VLOOKUP be able to work. paste this in some cell and see what's going on ={sheet2!B1:B\sheet2!A1:B} – user0 Jan 30 at 13:12
  • Your solution seems to work fine in test environment. See here: docs.google.com/spreadsheets/d/… – klor Jan 30 at 23:46
0

Here's a sample copy of your sheet: Index-Match

I suggest, instead of a VLookup, you use a Index/Match variant. This will allow you to search by any row, to return any column. Here's how:

The Match Function will return the row # of a specific column. You use this within the next function to create a crosshair target focusing on your desired value.

=(MATCH(A4;sheet2!B:B)) 

Where A4 is your lookup value, sheet2!B:B the column you're using to return the row # (your vertical crosshair).

You nest that inside the Index function which will return your horizontal crosshair and the value where the two points intersect:

=(INDEX(sheet2!A:B;###;1))

Where sheet2!A:B is your lookup range, ### is your row number, and 1 is the column from which you want a value returned (left to right columns are counted numerically 1 through however many columns you have in your specified range).

Where ### can be replaced by either the row number of the value you want returned or the match function above (MATCH(A4;sheet2!B:B)).

This way, you don't have to clean your data, you can just run searches with your crosshair and find your data.

  • I added some examples/tests to the demo table, and your formula fails in some conditions. Additionally your one is more difficult to remember, when I will need it again. – klor Jan 30 at 23:44
  • See here: docs.google.com/spreadsheets/d/… – klor Jan 30 at 23:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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