Finding words and copying adjacent cell

Originally Posted by Domski

Hi,

I think VLOOKUP should do the trick but can you upload a sample workbook showing your data layout and desired result.

Dom

Here is an example of what I am trying to do. The real file contains alot more data. I want C2 and C3 in UI-sheet to be filled with data from Data-sheet. I have a drop down menu for fruit and color. If I pick "red" as color and "apple" as fruit, I want C2 and C3 in UI-sheet to be 4 and Yes.

I tried the VLOOKUP but I didn't know how to copy the values in the adjacent cells.

Works great! Thank you so much!

Your formula works great, the only problem is that it returns #N/A if there are no yellow apples. I managed to write this formula to remove the #N/A, but now it returns a 0 if a yellow apple is found, but with no numer or Yes/No.

=IF(ISERROR(VLOOKUP($C$2&" "&$A$3;Data!$A$1:$O$200;5;FALSE));"";VLOOKUP($C$2&" "&$A$3;Data!$A$1:$O$200;5;FALSE))

I need a formula that returns blank for either an error (#N/A) or an empty cell.

Now I get the #N/A error again when VLOOKUP does not find anything, strange...

=IF(OR(ISERROR(VLOOKUP($C$2&" "&$A$3;Data!$A$1:$O$200;5;FALSE));VLOOKUP($C$2&" "&$A$3;Data!$A$1:$O$200;5;FALSE)="");"";VLOOKUP($C$2&" "&$A$3;Data!$A$1:$O$200;5;FALSE))

I also changed the =0 to ="" since some values can actually be 0. I had a problem with VLOOKUP returning 0 when the cell was empty.

Did this instead, now it works:

=IF(ISNA(VLOOKUP(C$2&" "&$A$3;Data!$A$1:$O$200;5;FALSE));"";VLOOKUP(C$2&" "&$A$3;Data!$A$1:$O$200;5;FALSE))

Okey, now I finally solved it;

=IF(ISNA(VLOOKUP(C$2&" "&$A$3;Data!$A$1:$O$200;5;FALSE));"";IF(VLOOKUP(C$2&" "&$A$3;Data!$A$1:$O$200;5;FALSE)="";"";VLOOKUP(C$2&" "&$A$3;Data!$A$1:$O$200;5;FALSE)))

It will return blank if it is #N/Aor an empty cell. I used IF(logic, false, (IF(logic, false, true))