Lookup from a separate sheet

[David T.]

I am looking for a formula that will look up in a given text in a specific column on a different tab and return the corresponding answer from different column. All answer that appliy.

[TMS]

You can probably make use of VLOOKUP

Regards

[David T.]

I tried that one but no luck.

[alansidman]

David, you might be able to get more assistance if you were more specific about your issue. You have provided only general issues so you have recieved only general responses. The more details you provide, the more specific the solutions. Want to try this again?

Alan

[TMS]

To pick up on Alan's point, a sample workbook with typical data would be helpful with a succinct definition of your requirements.

It may be that, if you are looking up something in column C but want data returned from column A or B, you could use INDEX/MATCH.

At the moment it's difficult to guess at your exact needs and why VLOOKUP isn't appropriate.

Regards

[NBVC]

e.g.

=Vlookup(A1,'Sheet2'!A:B,2,false)

looks up value in A1 of active sheet, finds it in column A of Sheet2, and returns value from 2nd column in range A:B, ie. from Column B

[David T.]

From sheet 2, I want to find all entries in column A of sheet 1 with "excess" in it and retreive the data in column E that correspondes with data in column Aand return it to sheet 2 with all of the data.

[NBVC]

Try this:

In G1 of Sheet1 enter:

=COUNTIF($A$1:A1,"Excess")

and copy down

in H1 enter:

=MAX(G:G)

Then in Sheet2, A2 enter:

=IF(ROWS($A$1:$A1)>Sheet1!$H$1,"",INDEX(Sheet1!E:E,MATCH(ROWS($A$1:$A1),Sheet1!$G:$G)))

copied down as far as you want.

[David T.]

Sorry, no luck with this one.

[ChemistB]

NBVC forgot a 0

=IF(ROWS($A$1:$A1)>Sheet1!$H$1,"",INDEX(Sheet1!E:E,MATCH(ROWS($A$1:$A1),Sheet1!$G:$G,0)))

See attached.

[NBVC]

Thanks Chemist.. you are correct... should've double checked the results.... :oops: