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
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!