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

Answered - Pending Review


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



  • 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.


  • 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")



Sign In or Register to comment.