How to get values from different sheets in excel

Question

I have 2 tabs in excel sheet . One tab contains study ID and other tab contains study details along with the ID. I want to get the data related to study ID into tab 1.

Sample data Tab 1

ID V1234

Tab 2

study ID Compound_Index Route_Index V1234 123654 PO

I want to combine them into one table

I have used the following

=IF($B4>0,INDIRECT("'sheet2'!D4"&B4)," ")

B4 is where V1234 is present in tab1 and D4 is where compound Index is present. I am getting # ref error

Answer 1

If you were going to use Vlookup, you need to use it like this -

=VLOOKUP(B4,Sheet2!$B$1:$D$10,3,FALSE)

Where you're looking up B4 in column B of Sheet2 and then returning column D.

Answer 2

The INDIRECT function requires the first argument to be a valid reference.

Your example has "'sheet2'!D4"&B4 as the first argument. The quote marks cause the data between them to be taken as a string, and the &B4 adds the contents of cell B4 to the end of the string, resulting in 'sheet2'!D4V1234 which is not a valid reference.

As Raystafarian and Web Guy brought up, Vlookup would be a better choice than Indirect for your example, due to its comparison ability.

Answer 3

There is VLookup (and table arrays) which is pretty handy and would get the results while being able to pull many values

Video: https://www.youtube.com/watch?v=oo7lzTp9sLU

Explination: http://computers.tutsplus.com/tutorials/how-to-extract-data-from-a-spreadsheet-using-vlookup-match-and-index--cms-20641