I need assistance with a lookup formula
The current formula I have is a Vlookup, however, when the Libreoffice calc workbook gets converted to Excel the Vlookup doesn’t work. The office that processes the workbook has to save the document for audit and they have excel so this is causing a problem…
Lookup formulas are working so I’ve converted them all except one I can’t seem to get to work.
Old formula: =IF(ISERROR(VLOOKUP($B31,vwc,2,0)),"",(VLOOKUP($B31,vwc,2,0)))
VWC was the VLOOKUP named range
New formula: =IF(ISERROR(LOOKUP($B31,$AB$12:$AC$22)),"",(LOOKUP($B31,$AB$12:$AC$22)))
It works except some of the items that could be in B31 are a mixture of text and number. The items that are just numbers (975, 974, for example —work fine and the return from the look up is correct. The problem comes that some values that can be entered into B31 (WO701 or 975 MN for example) the return is blank instead of what is in the table. I’m not sure how to fix it. This is the table:
975 WH (First 2 hours)
974 WH (each add’l hour)
97850 PR (FL first hour)
97851 PR (FL each add’l 30 min)
97852 PR (FL first hour)
97853 PR (FL each add’l 30 min)
WO710 Oh
WO702 Oh Comprehensive
WO703 Oh each add’l hour
975 MN W (First 2 hours)
974 MN W (each add’l hour)
If I enter W0710 in B 31 the return in C31 where the formula is is blank and not Oh . Same if I put in 975 MN C 31 is blank and not W (First 2 hours). If I put in 975 the return in C31 is correct and it says WH (First 2 hours) just like it should