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

jan aelbrecht

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

jan 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

Dale 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 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