Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula for Vlookup to refer more than 2 excel sheets?
i need to know what will be the formula for refering 3 spreadsheets in Vlookup?
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula for Vlookup to refer more than 2 excel sheets?
VLOOKUP both the lookup range and the return range are together on the same sheet. LOOKUP can do what you want, but your lookup range MUST be sorted in ascending order to work. =LOOKUP(A1,Sheet2!B1:B5,Sheet3!C5:C9) Note, even though the lookup range and the return range are on separate sheets, they HAVE to be exactly the same size, too, in the example 5 cells. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46282 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula for Vlookup to refer more than 2 excel sheets?
"jojo" wrote:
i need to know what will be the formula for refering 3 spreadsheets in Vlookup? Assume Sheet2, Sheet3 & Sheet4 (identically structured) contain the vlookup reference table in cols A & B Assume you want to vlookup the 3 sheets in this sequence: Sheet2 first, then Sheet3, then Sheet4 In Sheet1, you have the lookup values in A2 down You could put this in B2 (all in one cell, decomposed for clarity): = IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,0)), IF(ISNA(VLOOKUP(A2,Sheet3!A:B,2,0)), IF(ISNA(VLOOKUP(A2,Sheet4!A:B,2,0)),"", VLOOKUP(A2,Sheet4!A:B,2,0)), VLOOKUP(A2,Sheet3!A:B,2,0)), VLOOKUP(A2,Sheet2!A:B,2,0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find out which sheets refer to a cel | Excel Worksheet Functions | |||
refer to sequential sheets in a formula | Excel Worksheet Functions | |||
#REF in Sheets that refer to Pivot Tables | Excel Discussion (Misc queries) | |||
How do I refer to the tab name in a cell formula in Excel? | Excel Discussion (Misc queries) | |||
Excel should let me refer to the same formula on multiple sheets | Excel Worksheet Functions |