vlookup value 1 is invoice then Credit otherwise Debit from other sheet
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
Best Answer
-
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
Answers
-
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
-
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
Help Article Resources
Categories
Check out the Formula Handbook template!