Vlookup across multiple sheets based on cell value

[dan4]

Hello, can someone please advise a formula to use vlookup based on a cell value and different sheets. For example, I have three worksheets in one excel file:
Worksheet A = Master
Worksheet B = MFG
Worksheet C = Buy

Question:
If Cell C4 in master worksheet = "ZFIN", Vlookup value from master worksheet A1 in worksheet "MFG" as the table array, and return col_index_num 5 from the MFG worksheet. However, if Cell C4 in master worksheet = "BUY", Vlookup value from master worksheet A1 in worksheet "BUY" as the table array, and return col_index_num 5 from the BUY worksheet.

 

[Joe4]

You should be able to do something structured like this:

=IF(C4="ZFIN",VLOOKUP(...),IF(C4="BUY",VLOOKUP(...),""))

when the blue VLOOKUP is your VLOOKUP formula to the "MFG" sheet, and the red VLOOKUP is your VLOOKUP formula to the "BUY" sheet

 

[etaf]

=if( master!C4="ZFIN", Vlookup(master!A1,MFG!Table range,5,false), IF( master!C4="BUY", Vlookup(master!A1,BUY!Table range,5,false)))
is that what you mean
i'm not following

in worksheet "BUY" as the table array,

has the BUY sheet got a name range also called BUY ?