Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOK with Index - How to get results across instead of down?
In one workbook, I have 4 sheets of data and one reference table. The data
tables have: A B C 123 47 Text 123 47 Text 456 55 Text 455 66 Text 888 07 Text The reference table has in a list values that match the values from column A in the data sheets. A 123 456 455 888 In the reference table (Sheet), in column B I am using: =LOOKUP(A1,Sheet2!A1:B2,2,FALSE) to find values in Sheet2 and return cell B. Also in the refrence table, in column C I have: =INDEX('46501'!$I$2:$K$5,SMALL(IF('46501'!$I$2:$I$ 5=$A$1,ROW('46501'!$I$2:$I$5)),ROW(1:1)),3) Dragging this formula down, I can get the values in Cell B from sheet 2 that match.. How do I get them going across instaed of down and is there a way to do the search on the other 3 sheets at once? Cannot combine them cause will not fit in one worksheet. Please help. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOK with Index - How to get results across instead of down?
I don't have Excel installed on my new computer, so I can't test this, but
I'd say try replacing the 'Row' with 'Column'. The absolute references are there. I suspect that will work. I guess, try and see... -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "BobbyRT" wrote: In one workbook, I have 4 sheets of data and one reference table. The data tables have: A B C 123 47 Text 123 47 Text 456 55 Text 455 66 Text 888 07 Text The reference table has in a list values that match the values from column A in the data sheets. A 123 456 455 888 In the reference table (Sheet), in column B I am using: =LOOKUP(A1,Sheet2!A1:B2,2,FALSE) to find values in Sheet2 and return cell B. Also in the refrence table, in column C I have: =INDEX('46501'!$I$2:$K$5,SMALL(IF('46501'!$I$2:$I$ 5=$A$1,ROW('46501'!$I$2:$I$5)),ROW(1:1)),3) Dragging this formula down, I can get the values in Cell B from sheet 2 that match.. How do I get them going across instaed of down and is there a way to do the search on the other 3 sheets at once? Cannot combine them cause will not fit in one worksheet. Please help. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOK with Index - How to get results across instead of down?
Thanks for replying. It does work but only if I create it a separate
spreadsheet. Is there a way I can set it up, so that in the sheet with the reference data (which is in column A), I can have the VLookup in column B and Index in column C? Right now I am getting an #NUM! error And I made few typos in original post, when I was trying to simplify some of the table names. Here are the real formulas with the updated COLUMN. =VLOOKUP(Sheet1!A1,'46500'!H2:J5,3, FALSE) and =INDEX('46500'!$H$2:$J$5,SMALL(IF('46500'!$H$2:$J$ 5=Sheet1!C1,ROW('46500'!$J$2:$J$5)),COLUMN(C:C)),2 ) -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "BobbyRT" wrote: In one workbook, I have 4 sheets of data and one reference table. The data tables have: A B C 123 47 Text 123 47 Text 456 55 Text 455 66 Text 888 07 Text The reference table has in a list values that match the values from column A in the data sheets. A 123 456 455 888 In the reference table (Sheet), in column B I am using: =LOOKUP(A1,Sheet2!A1:B2,2,FALSE) to find values in Sheet2 and return cell B. Also in the refrence table, in column C I have: =INDEX('46501'!$I$2:$K$5,SMALL(IF('46501'!$I$2:$I$ 5=$A$1,ROW('46501'!$I$2:$I$5)),ROW(1:1)),3) Dragging this formula down, I can get the values in Cell B from sheet 2 that match.. How do I get them going across instaed of down and is there a way to do the search on the other 3 sheets at once? Cannot combine them cause will not fit in one worksheet. Please help. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOK with Index - How to get results across instead of down?
I found my mistakes. Thanks again. FOr anyoen reading this, I used:
=VLOOKUP(Sheet1!A1,'46500'!H2:I200,2, FALSE) =INDEX('46500'!$H$2:$I$200,SMALL(IF('46500'!$H$2:$ I$200=Sheet1!$A$1,ROW('46500'!$H$2:$I$200)),COLUMN (B:B)),2) "ryguy7272" wrote: I don't have Excel installed on my new computer, so I can't test this, but I'd say try replacing the 'Row' with 'Column'. The absolute references are there. I suspect that will work. I guess, try and see... -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "BobbyRT" wrote: In one workbook, I have 4 sheets of data and one reference table. The data tables have: A B C 123 47 Text 123 47 Text 456 55 Text 455 66 Text 888 07 Text The reference table has in a list values that match the values from column A in the data sheets. A 123 456 455 888 In the reference table (Sheet), in column B I am using: =LOOKUP(A1,Sheet2!A1:B2,2,FALSE) to find values in Sheet2 and return cell B. Also in the refrence table, in column C I have: =INDEX('46501'!$I$2:$K$5,SMALL(IF('46501'!$I$2:$I$ 5=$A$1,ROW('46501'!$I$2:$I$5)),ROW(1:1)),3) Dragging this formula down, I can get the values in Cell B from sheet 2 that match.. How do I get them going across instaed of down and is there a way to do the search on the other 3 sheets at once? Cannot combine them cause will not fit in one worksheet. Please help. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
Copying forumla for vlook up but changing the column Index # | Excel Worksheet Functions | |||
vlook/match/index | Excel Discussion (Misc queries) | |||
Vlook up or index match | Excel Worksheet Functions |