Excel Vlookup Question

[Airbiskit]

Trying to create a formula that looks at a cell and depending on whats in there looks up a certain data range.

In the cell I would either type in COST or REV.

If I typed in COST then a VLOOKUP further down the sheet would only look at a cell range.

Im okay with the vlookup its incorporating an IF statement that i am unsure of.

Much Appreciated

Steve

 

[xlbo]

Need a bit more info but it sounds like you should utilise the INDIRECT function - this converts a string (text) to a range reference eg if you switch cell is A2 (ie you enter "COST" or "REV" in A2, then in A3, you may use:

=VLOOKUP(A1,INDIRECT(A2),2,false)

where you have named ranges of "COST" and "REV". If you do not have these then you may use:

=VLOOKUP(A1,if(A2="COST",INDIRECT("CostSheet!$A$1:$z$100"),INDIRECT("RevSheet!$A$1:$Z$100"),2,false)



Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question