1

I would like to use IF and VLOOKUP as a test criteria and in the THEN field I would like to set the same VLOOKUP result that I used in the Test field.

Example:

=IF(VLOOKUP(A1;C1:E10;2;0)<>"something";VLOOKUP(A1;C1:E10;2;0);"NOT")

It works, but I want to change this formula above because it process two times the same VLOOKUP. I will use it in a sheet in 30 thousand cells. My solution is too slow. There is any one more efficient?

Sample data: First Table have about 17000 rows

| CONFERENCE | JOURNAL |
| IEEE A4    |         |
|            | ACM19   |

And in second table i want to do:

=IF(VLOOKUP($A3:$A;Table1!$A$2:$L$500;4;0)="";VLOOKUP($A3:$A;Table1!$A$2:$L$500;5;0);VLOOKUP($A3:$A;Table1!$A$2:$L$500;4;0));""))

I want to put in the cells if the field conference is empty, the Journal column value, if not, the Conference column value, a thousand times.

Improve this question
6
  • 1
    Are you able to use a helper cell/column? You can run the VLOOKUP in it's own cell and then reference the result in your IF.
    – Worthwelle
    Commented Mar 22, 2019 at 19:40
  • Yes, I can, but I don't known if this is a good solution, because it will add more 30 thousand more cells with formulas to be calculated every time. But is more efficient than using double VLOOKUPS.
    – oaugustopro
    Commented Mar 23, 2019 at 15:57
  • Perhaps if you give us sample data and explain what your are trying to do it would be easier to find a more efficient solution?
    – cybernetic.nomad
    Commented Mar 23, 2019 at 20:44
  • Sure, I will add some data editing the question.
    – oaugustopro
    Commented Mar 23, 2019 at 21:31
  • Are there cases with both columns (Conference and Journal) are filled? What about both being empty?
    – cybernetic.nomad
    Commented Mar 23, 2019 at 22:41

1 Answer 1

Reset to default
1

If there is only one value in columns D and E, then there is no need for an IF statement if you concatenate both VLOOKUP results:

=VLOOKUP(A2,Table1!$A$2:$L$500,4) & VLOOKUP(A2,Table1!$A$2:$L$500,5)

Since one VLOOKUP will always return an empty string, the result will automatically be the other string.

Improve this answer
1
  • Thank you very much! Perfect solution!
    – oaugustopro
    Commented Mar 25, 2019 at 16:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .