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. |
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. |
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 ... 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. |
All times are GMT +1. The time now is 08:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com