vlookup value 1 is invoice then Credit otherwise Debit from other sheet

09/20/21
Answered - Pending Review

Hi,

I have 2 sheets.

sheet 1 = field "INV/CN" = INVOICE or CREDIT NOTE

field "ICO Number" unique number


in sheet 2 I have these 2 query's

cell Debit / Credit => =VLOOKUP([ICO Number]@row; {ICO Header Range 1}; 2; false) = INVOICE

cell Result => =IF([Debit / Credit]@row = "INVOICE"; "Credit"; "Debit") = Credit


Question is how can I make this in one go ? combination VLOOKUP with the IF


regards

Answers

  • Dale MurphyDale Murphy ✭✭✭✭✭

    @jan aelbrecht

    I would need more info to build the formula, but I recommend exploring the use of INDEX with either MATCH and/or COLLECT. I find INDEX more reliable than Vlookup, and also find that the additional use of Collect (for e.g.) can be very powerful.

    Dale

  • jan aelbrechtjan aelbrecht ✭✭✭✭✭

    Hi Dale,


    thanks for your suggestion, I have now a working formula like this :


    =IF(INDEX({ICO Header Range 2}; MATCH([ICO Number]@row; {ICO Header Range 3}; 0); 1) = "INVOICE"; "Credit"; "Debit")


    regards

    Jan

Sign In or Register to comment.