IF/VLOOKUP Statement

[u0ajp2]

Hi

Hopefully somebody can help me as my eyes are beginning to burn I have been looking at this so long!

Basically I am exporting data from another programme in to Excel and need to perform a less than ordinary (for me) VLOOKUP to another workbook.

The aim is to look up the value of column H in workbook A, and depending on the text contained in column C return the value of different columns of workbook B.

Eg. If column C contains the text JNT, i want to return column 21, or if it contains CAB i want to return column 25.

Here are some examples of what I have tried so far:

• =VLOOKUP(H3,'[2010 work programme Ap.xls]Capital EAST in detail'!$A:$Z,IF(C3="JNT",21,IF(C3="FIT",22,IF(C3="OHL",23,IF(C3="RC",24,IF(C3="CAB",25,IF(C3="CIV",26,14)))))),FALSE)
• =IF(SEARCH("JNT",$C3),VLOOKUP($H3,'[2010 work programme Ap.xls]Capital EAST in detail'!$A:$Z,21,FALSE),IF(SEARCH("FIT",$C3),VLOOKUP($H3,'[2010 work programme Ap.xls]Capital EAST in detail'!$A:$Z,22,FALSE),IF(SEARCH("OHL",$C3),VLOOKUP($H3,'[2010 work programme Ap.xls]Capital EAST in detail'!$A:$Z,23,FALSE),IF(SEARCH("CAB",$C3),VLOOKUP($H3,'[2010 work programme Ap.xls]Capital EAST in detail'!$A:$Z,25,FALSE),IF(SEARCH("CIV",$C3),VLOOKUP($H3,'[2010 work programme Ap.xls]Capital EAST in detail'!$A:$Z,26,FALSE),"X")))))
• =VLOOKUP(H3,'[2010 work programme Ap.xls]Capital EAST in detail'!$A:$Z,IF(SEARCH("JNT",C3),21,IF(SEARCH("FIT",C3),22,IF(SEARCH("OHL",C3),23,IF(SEARCH("CAB",C3),25,IF(SEARCH("CIV",C3),26,14))))),FALSE)

I'm sure there will be a way of doing this, but my mind is drawing a blank! I would potentially like to put this in VBA so it can be used on multiple spreadsheets (all will have exactly the same format and look up the same values.).

Thanks in anticipation.

Andy

 

[u0ajp2]

Thanks Dan, i'll check out that thread.

I think a possible work around would be to do the IF(NOT(ISERROR(SEARCH statements in one column to identify the column to look up and then use the VLOOKUP in a second referencing the first column. Not ideal but a potential workaround, unless I can find something in VBA which will do the job.

 

[Dan Waterloo]

On that thread, I thought these two constructions in particular might prove fruitful in your situation:

=IF(A1="A",1,"")&IF(A1="B",2,"")&IF(A1="C",3,"")
    &IF(A1="D",4,"")&IF(A1="E",5,"")&IF(A1="F",6,"")
   &IF(A1="G",7,"")&IF(A1="H",8,"")&IF(A1="I",9,"")
   &IF(A1="J",10,"")   (This method is not limited to 30 comparisons.)

and

=(A1="A")*1+(A1="B")*2+(A1="C")*3+(A1="D")*4+(A1="E")*5
+(A1="F")*6+(A1="G")*7+(A1="H")*8+(A1="I")*9+(A1="J")*10

In your case, it would look something like

=VLOOKUP(H3,'[2010 work programme Ap.xls]Capital EAST in detail'!$A:$Z, 
NOT(ISERROR(SEARCH("JNT", $A2)))*21 
+ NOT(ISERROR(SEARCH("FIT", $A2)))*22 
+ NOT(ISERROR(SEARCH("OHL", $A2)))*23 
+ NOT(ISERROR(SEARCH("RC", $A2)))*24 
+ NOT(ISERROR(SEARCH("CAB", $A2)))*25 
+ NOT(ISERROR(SEARCH("CIV", $A2)))*21,  FALSE)

A risk would be that the string being searched would contain BOTH JNT and FIT and then that subformula would return 21 + 22 and cause the VLOOKUP to go way out of range. You can judge whether that's likely or not.

The other risk is the the string being searched doesn't contain ANY of these substrings. The n the subformula would return 0 and again cause a VLOOKUP error.