CODEWITHSUNDEEP
google-sheets
Wish
Wish

Reputation: 163

Google spreadsheet Multiple result with VLOOK UP

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

Upvotes: 0

Views: 585

Answers (1)

dePatinkin
dePatinkin

Reputation: 2289

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.

Upvotes: 1

Related Questions