Ignoring values with zeros

**exdox77** · 02-06-2013, 09:43 PM

I've been using vlookup to find values in a different sheet. Working fine up till recently where my reports have had zeros in them. What I need help with is that I need to figure a way to skip the row with the value of zero.

Example :

A1 B2
Name 0
Name 2
Name2 3
Name3 4

I've tried index with match but match kept coming back n/a. All I need is to look up the value given in column A and return the value in column B skipping any zeros.

Any guidance would be helpful. Thank you.

**Tony Valko** · 02-06-2013, 09:51 PM

Let's see if I understand what you're asking here.

If you want to lookup "name" the result you expect is 2 and not 0?

**benishiryo** · 02-06-2013, 09:51 PM

hi exdox77, welcome to the forum. assuming data in A1:B4, try this array formula:
=INDEX($B$1:$B$4,MATCH("Name",IF($B$1:$B$4<>0,$A$1:$A$4),0))

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it

**exdox77** · 02-06-2013, 10:32 PM

Adherence Comparison 1.17.2013.xlsx

I have attached the spreadsheet I have been working on. I tried the formula but it came back n/a.

The spreadsheet has tabs at the bottom the ClockIn tab references Timesheet Data, which is where ClockIn gets its time from. The error I am encountering are the 0:00 in times on the Timesheet Data.

This is the formula I used and used CTRL + SHIFT + ENTER

=INDEX('Timesheet Data'!$S$2:$T$946,MATCH($A36&" "&C$1,IF('Timesheet Data'!$T$2:$T$946<>0,'Timesheet Data'!$T$2:$S$946),0))