A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Vlookup across multiple tabs



 
 
Thread Tools Display Modes
  #1  
Old July 20th 05, 04:29 PM
Hirsch
external usenet poster
 
Posts: n/a
Default Vlookup across multiple tabs

Is there a formula to incorporate a vlookup scanning multiple tabs.

i.e.
Total Tab will scan "GMP" "GMT" "FMP" & "TRN"

returning #n/a for the missing information and the first line item found on
the rest.

Thanks in advance.
Ads
  #2  
Old July 20th 05, 04:48 PM
bj
external usenet poster
 
Posts: n/a
Default

unfortunaltely, the best metod I know of is
=if(iserror(Vlookup(ref,GMP!range,2,0)),if(iserror (Vlookup(ref,FMP!range,2,0)),if(iserror(...)
...,Vlookup(ref,FMP!range,2,0),Vlookup(ref,GMP!ran ge,2,0))))


"Hirsch" wrote:

> Is there a formula to incorporate a vlookup scanning multiple tabs.
>
> i.e.
> Total Tab will scan "GMP" "GMT" "FMP" & "TRN"
>
> returning #n/a for the missing information and the first line item found on
> the rest.
>
> Thanks in advance.

  #3  
Old July 20th 05, 07:42 PM
Biff
external usenet poster
 
Posts: n/a
Default

Hi!

Make a list of the sheet names:

J1 = GMP
J2 = GMT
J3 = FMP
J4 = TRN

Name that list something like SList.

This will work as long as the table array is the same on each sheet.

Enetered as an array using the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SList,MATCH(TRUE,CO UNTIF(INDIRECT("'"&SList&"'!A1:A10"),A1)>0,0))&"'! A1:C10"),2,0)

A1 is the lookup value

This is how that formula would look as it operates on a single sheet:

=VLOOKUP(A1,GMP!A1:C10,2,0)

Biff

"Hirsch" > wrote in message
news
> Is there a formula to incorporate a vlookup scanning multiple tabs.
>
> i.e.
> Total Tab will scan "GMP" "GMT" "FMP" & "TRN"
>
> returning #n/a for the missing information and the first line item found
> on
> the rest.
>
> Thanks in advance.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I use vlookup for multiple occurrences of the same value Edith F Excel Worksheet Functions 15 April 29th 05 06:12 PM
how do I use vlookup for multiple occurrences of the same value bj Excel Worksheet Functions 0 April 27th 05 10:43 PM
Is there a way to have multiple rows of tabs? Peteewj Excel Discussion (Misc queries) 2 February 24th 05 11:41 PM
Can VLOOKUP return multiple answers based on several identical lo. jddtct Excel Worksheet Functions 3 January 11th 05 07:03 AM
vlookup over multiple worksheets Neil Excel Worksheet Functions 3 December 16th 04 08:19 PM


All times are GMT +1. The time now is 08:23 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.