Google spreadsheet Multiple result with VLOOK UP

0
votes

The VLOOKUP formulas which works individually are

=if(VLOOKUP(E2,DB!$C:$E,1,0)>0,"COMPLETED",)
=if(VLOOKUP(E2,DB!$F:$H,1,0)>0,"IN PROGRESS",)

The issue is while displaying both results in a single cell, the formula which I came up for this was 

C2=if(AND(VLOOKUP(E4,DB!$C:$E,1,0)>0),"COMPLETED",if(VLOOKUP(E4,DB!$F:$H,1,0)>0,"IN PROGRESS","UNDEFINED"))

I have tested the formula with normal conditions other than VLOOKUP and it works without any issues, not sure what's wrong with it.

Example : C10=if(AND(E10=1),"ONE",if(E10=2,"TWO","NO DATA"))

Any help appreciated. May be its something simple but I am pulling my hair out for the last 3 hours.

Thanks :)
/-----------------------/

Updated 03.05.2016

Sorry for the comments that I have posted as I am new at using Stack overflow.

I have tried with only IF statements without any AND conditions, but the result is still same. The VLOOKUP is not returning the second value.

 C15=IF(VLOOKUP(E15,DB!$F:$H,1,0)>0,"COMPLETED",if(VLOOKUP(E15,DB!$F:$H,1,0)>0,"IN PROGRESS","UNDEFINED"))

What I am expecting in cell C2 (sheet1) is check the values in cell E2 against the columns C:H ( Sheet 2/ DB). If it belongs to Column C:C in (sheet2/DB) then the value in C2 (sheet1) should display as "Completed" else if the value is in column F:F ( sheet2/DB) then in C2 (sheet1) should display "In Progress".

Link to my spreadsheet link

1
google-sheets
VLOOKUP throws N/A if the value isn't found. You can use if(isna(vlookup(...)), "something", "something else") to handle this. – user3717023
I also don't understand why you are using and with one boolean argument. – user3717023
Your data structure is not clear. Vlookup is not being used correctly. AND() is not being used in any way that makes sense. Vlookup looks in the first column of a range only. Your formula does not look in range C to E. At all. Instead of getting lost in wrong formulas, please edit your question, provide a data sample, provide the desired result and the logic that leads to that. Again, edit the question. Don't put that into a comment. Post a comment after you've added more detail to notify people who are following the question. – teylyn
The tags excel-formula, vlookup and conditional-formatting are related to Excel, not to Google Sheets and neither to spreadsheets apps in general. – Rubén

1 Answers

1
votes

There are a few problems with your formula.

  1. VLOOKUP and similar functions search within a single row or column, and it seems like you're looking for your value in multiple rows and columns.
  2. As @Meta mentioned, VLOOKUP returns N/A when the value is not found in the range, and you are expecting a zero or less value to be returned (when you check for >0 in the IF statement). Note that VLOOKUP returns the cell value itself and not an index of a match (like the MATCH function).

My suggestion is to replace your VLOOKUPs with COUNTIF.

=IF(COUNTIF(DB!$F:$H,E2)>0,"COMPLETED",IF(COUNTIF(DB!$C:$D,E2)>0,"IN PROGRESS","UNDEFINED"))

COUNTIF counts in multiple rows and columns, and will return a zero if no matches are found.