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);"")
- Excel Formula changes values after a day or so
- 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
- Can i change this formula to tracks from a start and finish date instead of a list of dates in a column?
- How to coerce text using IFS function in Google Sheets?
- Find match in a specified column, then find match in the matched row?
- How to split a cell text by delimiter, do a lookup into given data set using the split cell text, and print in another sheet
- Adding Vlookup + Vlookup with active cell
- Fill in blank cells in ={ARRAYFORMULA()}
- how to search for a cell and grab the next cell data
- Find MAX and MIN using Excel VLOOKUP by matching the Rows and Results the matching First Column
- Check if a Cell Value is between Two Values using Vlookup
- Pull most recent data from one Google Sheet to another