Quick VLOOKUP Question

[posracer30]

Is there a way to use an IF statement within a VLOOKUP that will return a different column depending on user's request?

Example:
User selects Fund name, and Checklist Type.
The VLOOKUP needs to select either 1,2,3,4 for the column index number depending on which type of Checklist is selected.

My current formula is this (but returns #REF!)
=VLOOKUP($B$3&"*",Summary!A14:A84,(IF($C$3="CSS",4,IF($C$3="FR",5,IF($C$3="T&R",6,7)))),FALSE)

 

[Jonmo1]

Welcome to the board....

The table you're looking into needs to be wide enough to occomodate the column Ref #. So if your IF question results in 7, you're lookup array needs to be 7 columns wide..

replace
Summary!A14:A84
with
Summary!A14:G84

Hope that helps...

 

[posracer30]

yes, figured out that one shortly thereafter... oops.
thank you though!

 

[mansoormanzoor]

Updating Column Index Number in VLOOKUP

Hi,

I have 12 rows for monthly budget in a separate worksheet starting from coulumns C. I use VLOOKUP to get value from budget sheet to compare with monthly actuals.In my Actuals sheet I use
VLOOKUP(A1,Budget!$C$1:$P$300,2,false) for January
VLOOKUP(A1,Budget!$C$1:$P$300,3,false) for February
VLOOKUP(A1,Budget!$C$1:$P$300,4,false) for March
and so on...

My problem is that each month I have to update my COLUMN_INDEX_NUMBER and then copy to different sheets(20 sheets) a 5-6 hours job.Is there any solution that I update link and it automatically updates the COLUMN_INDEX_NUMBER

Thanks

 

[DonkeyOte]

given your column index appears to go from 2 (Jan) to 13 (Dec) you could use something like:

for B1 (jan):

VLOOKUP($A1,Budget!$C$1:$P$300,COLUMN(B1),false)

when you copy this to C1 (Feb) the formula will become:

VLOOKUP($A1,Budget!$C$1:$P$300,COLUMN(C1),false)

and so on and so forth, the COLUMN identifier increments (2,3 etc..)

If you are only ever pulling ONE month then use a MATCH based on a criteria cell, ie let's say you insert a row and type in Jan-08 into cell A1, in B1 you have your vlookup criteria (previously A1), in B2:

=VLOOKUP($A2,Budget!$C$1:$P$300,MATCH($A$1,Budget!$1:$1,0),0)

The match will find the column position of Jan-08 on row 1 on Budget sheet.

HTH

 

[Cindy Ellis]

A fairly easy solution, if it meets your needs, would be to refer to the target month (+1) in the formula as the column_index.
For instance, if you enter the month number in cell A1 on a sheet called "Setup", all of the formulas could be changed once to refer to it as follows:

VLOOKUP(A1,Budget!$C$1:$P$300,Setup!$A$1+1,false)

All you have to do then is change the month number on the Setup sheet, and all formulas will now refer to the appropriate column.
If you already have the month name in a cell by itself, you could use this concept without adding a new cell. Just replace the $A$1 in the above formula with
MONTH($B$1 & "-1") ...assuming the month name was in cell B2, as follows:

VLOOKUP(A1,Budget!$C$1:$P$300,yoursheetname!MONTH($B$1 & "-1")+1,false)

Hope this helps,
Cindy

 

[mansoormanzoor]

Sorry for the late reply.

Its amazing,it worked.That's what I was looking for.

Thank you very much.

Mansoor