VLOOK with Index - How to get results across instead of down?

[BobbyRT]

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.

 

[ryguy7272]

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...

 

[BobbyRT]

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)
an
=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)

 

[BobbyRT]

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)