Remember Me?

#1
July 20th 05, 04:29 PM
 Hirsch Posts: n/a
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.

#2
July 20th 05, 04:48 PM
 bj Posts: n/a

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.

#3
July 20th 05, 07:42 PM
 Biff Posts: n/a

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Edith F Excel Worksheet Functions 15 April 29th 05 06:12 PM bj Excel Worksheet Functions 0 April 27th 05 10:43 PM Peteewj Excel Discussion (Misc queries) 2 February 25th 05 12:41 AM jddtct Excel Worksheet Functions 3 January 11th 05 08:03 AM Neil Excel Worksheet Functions 3 December 16th 04 09:19 PM

All times are GMT +1. The time now is 09:29 PM.