Vlookup Function

[nickharr]

I use the Vlookup function to look at a specific worksheet and I was wondering whether it can be used to look at more than one worksheet? If I have 3 worksheets with numbers in Column A - and comments in column B - would I be able to search for a recurring number in column A of the 3 worksheets and the return the comments from column B which would vary from one worksheet to the other? Maybe the Vlookup function is not what I need to be using. I would welcome any comments.

 

[Richard Schollar]

Hi

You would need to perform 3 separate Vlookups (one for each sheet).

 

[scottylad2]

I use the Vlookup function to look at a specific worksheet and I was wondering whether it can be used to look at more than one worksheet? If I have 3 worksheets with numbers in Column A - and comments in column B - would I be able to search for a recurring number in column A of the 3 worksheets and the return the comments from column B which would vary from one worksheet to the other? Maybe the Vlookup function is not what I need to be using. I would welcome any comments.

you could use the Vlookup, each table array would need to be set out identical,and each of them would need to have a name, for arguments sake call them CommentsOne CommentsTwo CommentsThree and each of the arrays cover A1:B100 the syntax is along the lines of this, with your lookup value in A2 =Vlookup(A2,Indirect(CommentsOne),2,0)You could house the table names in a drop down list say in B3 on your 1st workshet, then just reference that in the indirect

hopethat helps

 

[nickharr]

Thanks very much for the comments - I will now try these out! Just one other query - when vlookup returns #N/A when it finds nothing - is there a way of it showing a blank cell instead? It would just make my lists easier to read!

 

[Richard Schollar]

Nick

If using xl2007 or greater use:

=IFERROR(VLOOKUP(yourformulahere),"")

or using xl2003 or earlier:

=IF(ISNA(VLOOKUP()),"",VLOOKUP())

 

[nickharr]

Thanks Richard - that works a treat!