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

 

[lenze]

Hi and welcome to the board!!!
I agree that you will probably want a VBA solution!!
FIRST. You say different WORKBOOKS(A & B). Is that correct??
Can you post a small sample of your data?? IS the Data in Column "H" enter manually, imported or by formula? Column "C"? Are the values in Column "C" exactly those in your 1st formula, or can they be embedded in a string? I mean would it only contain "FIT" or could it have "XXXFITRRR"
lenze

 

[Dan Waterloo]

Hi u0ajp2,

I was able to get a simpler version of your first VLOOKUP to work.
I referred to a lookup table in another book and the column used depended on the value in one of input columnss:

=VLOOKUP(B4, 'C:\Documents and Settings\dg38\My Documents\REPORTS\[Tables.xls]Sheet1'!$A$1:$C$3, IF(A4="JNT", 2, IF(A4="KAT", 3, #N/A)))

It's simpler in that I used only two columns and a smaller lookup table.

I wonder about the way you have defined your lookup table:

$A:$Z

I have never done this or seen this. Try explicitly defining the rows as well as the columns and see if that helps your formula work better.

 

[u0ajp2]

Hi Dan & Lenze, thanks for your replies.

Dan I don't think your simplified VLOOKUP will work as the cell would only contain the text that I am using in the IF statement, eg. I would be looking for JNT, but the cell could contain BO.JNT, CV.JNT to name a few.

Lenze, yes i have 2 workbooks one called "2010 work programme Ap.xls]Capital EAST in detail'!" (Let's call this B) and another called K.00976-Activity Resource Assignments (Workbook A). In sheet A I want to VLOOKUP on column H (which matches column A in Workbook B), and then depending on the value in column C on Workbook A I want to return the value from different columns of Workbook B. Column C will only contain the text, so as explained above if I'm looking for JNT the cell may contain BO.JNT, CV.JNT etc... (All data in Workbook A comes from the export from another programme so no formulas are involved).

I will try to post some sample data for you.

Thanks for helping, it's much appreciated.

Andy

 

[u0ajp2]

This is a sample from K.00976-Activity Resource Assignments (Workbook A).

<TABLE style="WIDTH: 671pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=893 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 241pt; mso-width-source: userset; mso-width-alt: 11739" width=321><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD id=td_post_2235324 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17 x:str="'(*)Activity Status">(*)Activity Status</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=90 x:str="'Resource ID">Resource ID</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64 x:str="'Role ID">Role ID</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64 x:str="'Cost Account ID">Cost Account ID</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 241pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=321 x:str="'(*)Activity Name">(*)Activity Name</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64 x:str="'(*)Project ID">(*)Project ID</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98 x:str="'(*)WBS">(*)WBS</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64 x:str="'(*)Resource Type">(*)Resource Type</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64 x:str="'Budgeted Units(h)">Budgeted Units(h)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="'Not Started">Not Started</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="'BO.JNT">BO.JNT</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="'C04CX Install 100m 11KV Cable">C04CX Install 100m 11KV Cable</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="'K.00976">K.00976</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="'K.00976.C04CX">K.00976.C04CX</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="'Labor">Labor</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="'0.32">0.32</TD></TR></TBODY></TABLE>

And this is a sample from 2010 work programme Ap.xls]Capital EAST in detail'! (Workbook B).

<TABLE style="WIDTH: 2370pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=3154 border=0 x:str><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4032" width=126><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3232" width=101><COL style="WIDTH: 217pt; mso-width-source: userset; mso-width-alt: 9248" width=289><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2912" width=91><COL style="WIDTH: 244pt; mso-width-source: userset; mso-width-alt: 10400" width=325><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 4416" width=138><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4288" width=134><COL style="WIDTH: 206pt; mso-width-source: userset; mso-width-alt: 8768" width=274><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2688" width=84><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3552" width=111><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 3968" width=124><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3328" width=104><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2784" width=87><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 4480" width=140><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 2720" width=85><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2752" width=86><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2496" width=78><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3072" width=96><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 2816" width=88><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 2720" width=85><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2752" width=86><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2496" width=78><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3072" width=96><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 2816" width=88><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: purple" width=126 height=18>ProjectWBS</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=101>Project Def</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 217pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=289>Description</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=91>MU Code</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 244pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=325></TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=138>Activity Schedule</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=134>Investment Code</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 206pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=274>Investment Option</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=84>IO Code</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 83pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=111>Unique Ref</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 93pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=124>Delivery Centre</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 78pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=104>DC Name</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=87>IAP/EAP</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 105pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=140>2010 Macro Units</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=85>JTG Hours</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=80>FIT Hours</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=86>OHL Hours</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=78>RC Hours</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=96>Cable Hours</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=88>Civil Hours</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=85>JTG</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=80>FIT</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=86>OHL</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=78>RC</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=96>Cable</TD><TD class=xl84 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: purple" width=88>Civil</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=126 height=18>K.00827.C01C2X</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=101>K.00827</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 217pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=289>Thurnby Holmleigh Gdns 11kv Refurb.</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=91>C01C2X</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 244pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=325>Major Refurbishment 100m of 11KV OHL Cct</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=138>ANNUAL</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=134>6A</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 206pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=274>HON141 HV OH Major Refurbishment</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=84>HON141</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 83pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>HON141.C01C2X</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 93pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=124>20103552</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 78pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=104>Hinkley</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">EAP</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 105pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=140 x:num="17.1">17.1</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0.00</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0.00</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>2.40</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0.00</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0.00</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0.00</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=O2*$N2">0.00</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=P2*$N2">0.00</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=Q2*$N2">41.04</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=R2*$N2">0.00</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=S2*$N2">0.00</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=T2*$N2">0.00</TD></TR></TBODY></TABLE>

 

[Dan Waterloo]

Okay,

=VLOOKUP($B2, 'C:\Documents and Settings\dg38\My Documents\REPORTS\[Tables.xls]Sheet1'!$A$1:$C$3, IF(NOT(ISERROR(SEARCH("JNT",$A2))), 2, IF(NOT(ISERROR(SEARCH("KAT",$A2))), 3, #N/A)))

is also my simple version of your more complex question but also allows for BO.JNT, CV.JNT, etc, when looking for JNT. And it works for me.

 

[u0ajp2]

Thanks Dan, I can't seem to get it to work though, the statement returns either 2, 0 or #N/A, none of which are correct!

I'll keep trying!

 

[Dan Waterloo]

Two recommendations:

1. Try much reduced version of your formula so you can see where it's working and where it isn't. It's better to build mega-formulas from subformulas that you have tested and know that they work.

2. Use formula evaluation to see HOW your formula is being evaluated step by step. The Evaluate Formula button is on the Formula Auditing toolbar and you can bring that up from the menus in Excel 2003: /View/Toolbars and then ticking that particular toolbar.

Note that I wrapped a couple more functions around the SEARCH function:

IF(NOT(ISERROR(SEARCH("KAT",$A2))), 3, #N/A)

SERACH will return the position of "KAT" within the string being searched (as an integer), unless it doesn't find it at all, and then it returns #VALUE! By wrapping it in NOT-ISERROR, I'm getting the subformula to return TRUE if it has found in the string and FALSE if it hasn't. That's important. #VALUE! is not treated the same way as FALSE. I put the #N/A in to indicate that NONE of the the search strings you were looking for were found, but if you want that to default to a specific column in your version, go ahead. (So I am never returning integers or errors except for the one I planned for.)

 

[u0ajp2]

Thanks Dan.

I have now tried to create each IF(NOT(ISERROR(SEARCH statement seperately and then added them to a VLOOKUP one at a time. The formula works with 4 IF(NOT(ISERROR(SEARCH statements, but as soon as I add a 5th the formula fails!

Are you aware of any limitations to the number of arguments you can add to a statement like this? I require 5-6 search statements within the lookup!

Thanks for your help.

 

[Dan Waterloo]

Ah! So now you have isolated the problem!

PLease check this reference: http://spreadsheetpage.com/index.php/tip/alternatives_to_nested_if_functions/

It may well contain an idea that works for you.