Ask Your Question

Lookup formula assistance

asked 2018-03-06 17:25:36 +0200

Rockerdo gravatar image

updated 2018-03-06 17:26:59 +0200

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-03-06 19:36:52 +0200

erAck gravatar image

VLOOKUP with the 4th argument being 0 indicates that the lookup range does not have to be sorted. An omitted 4th parameter or with a value not 0 means the data has to be sorted ascending and a range lookup is performed (yielding the last value <= the query). LOOKUP always performs a range lookup and the data implicitly has to be sorted ascending and if it is not then the result returned may be arbitrary.

As a side note, instead of =IF(ISERROR(VLOOKUP($B31,vwc,2,0)),"",(VLOOKUP($B31,vwc,2,0))) you can write =IFERROR(VLOOKUP($B31,vwc,2,0),"") which doesn't need to execute the lookup twice, but more likely you want to test only for the #N/A error instead of any error, so =IFNA(VLOOKUP($B31,vwc,2,0),"")

converted to Excel the Vlookup doesn't work

What does not work? The result is ...?

edit flag offensive delete link more


I don't know why the Vlookup doesn't work when converted to excel. However, every single time excel is converting the named range to a table and if they save the workbook or click to enable the info that was entered disappears. When I changed my formulas to a simple Lookup versus V lookup that issue does not happen. Something is happening when Excel opens the converted file it can't read the Vlookups.
Funny is I have one made in Excel and one made specific for Libre. The set up for these

Rockerdo gravatar imageRockerdo ( 2018-03-06 23:27:45 +0200 )edit

Also the formulas and the set up for the Vlookups are exactly the same in the excel version versus the Libre version. I don't know why it can't read it in excel. If I open the converted Libre tool that is saved as XLS or XLSX in Libre no issue. Only if I open the XLSX or XLS Libre tool in full excel is there an issue.

Rockerdo gravatar imageRockerdo ( 2018-03-06 23:29:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-06 17:25:36 +0200

Seen: 75 times

Last updated: Mar 06 '18