LibreOffice Calc #N/A preventing summing after vlookup data is processed
How to get rid of the #N/A in G5? I know it's because there's no name in E5 for the vlookup function to produce a result, but I don't want to have to type every single
employee in column E and then manually write in 0 hours.
Cell G4 says (works)...
=F4*VLOOKUP(E4,$Sheet1.$B$4:$Sheet1.$C$6,2,0)
and of course G5 (doesn't work as mentioned above) says...
=F5*VLOOKUP(E5,$Sheet1.$B$4:$Sheet1.$C$6,2,0)
The #N/A in G5 prevents the sum formula in G7 working.
G7 says.... =SUM(G4:G6)
In G5 I've tried things like:
=if(or(e5="",f5=""),(F6*VLOOKUP(E6,$Sheet1.$B$4:$Sheet1.$C$6,2,0)),"")
But LO Calc Gives an Err:504
p.s. I know 'lookup' can be used here in this extract, but vlookup is being used in the actual master spreadsheet and I want to stick with that method
JohnSUN's comment answered my question. For me, it didn't require any explanation.
Solve the issue by pasting the IF variant in the cell where #N/A appeared....
=IFERROR(F4*VLOOKUP(E4;$Sheet1.$B$4:$Sheet1.$C$6;2;0);"")
- VBA Hide Rows in Sheet Based on the Vlookup Value in a Named Range
- Lookup matching cell in a table and return the value of the first cell that's merged upwards to the matching cell
- Find MAX and MIN using Excel VLOOKUP by matching the Rows and Results the matching First Column
- How to get last value from VLookup?
- Combine IF for text with INDEX and MATCH? Or VLOOKUP? QUERY?
- Excel VLOOKUP Doesn't Recognize Identical Cells
- How to inner-join in Excel (eg. using VLOOKUP)
- Dynamically averaging data Per ID Per Time period
- Three-dimensional referencing
- Excel Formula changes values after a day or so
- Pull data from one Sheet to another Sheet based on the Header names, headers in different order
- Invalid Cell Reference Error while vlookup?
- VLOOKUP Returns 0 even though there is a value in the line below the first result
- Google Sheets ARRAYFORMULA with Partial Match and Multiple Criteria Not Returning Expected Results
- Google spreadsheets "VLOOKUP evaluates to an out of bounds range."
- Vlookup for items in a comma separated list
- Create a formula that gathers information from one sheet and inputs it in another based on one cell
- Trouble with Vlookup on power queries?
- AND condition added to Array Formula doesn't work as expected
- VBA for Vlookup with multiple criteria
- Excel's max function returning wrong value when using vlookup
- Using Excel for VLOOKUP and WildCard Not Working
- Return offset cell value based on data validated dropdown list
- Vlookup - getting answer from month upon month data
- Excel search for certain text within cell and return vlookup from a range
- How to reset Week Number when the Year changes in Google Sheets
- Changing value in one column with sequential information when cell value changes in another column
- how to combine multiple value + text in 1 cell?
- Get all dates when a certain activity is booked in google sheets
- VLOOKUP help - Specific column search returns okay but multiple column says it does not exist