Combine List with a Vlookup

[mcable01]

Hi!

Is it possible to use a vlookup formula and, if no value is located, select from a drop-down list within the same cell?

Not sure if this is possible.

Hope this makes. Let me know if you need any other info.

Cheers!!

M

 

[Aladin Akyurek]

Woud you elabarate by way of an example?

 

[mcable01]

[TABLE="width: 448"]
<TBODY>[TR]
[TD="colspan: 2"]Sure, Ill try!</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]As part of the bank reconciliation process I'm allocating bank transactions to relevant departments on a monthly basis (so they know to investigate). When I run a tranx list for a new month some of the original transactions remain, as well as new additional ones for the month. As I've already allocated a dept to those transactions from the prior month, I want to use a Vlookup to do this. For any new transactions, I want to use the data validation list function to select from a list of departments. See below simplified example:</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD][/TD]
[TD]A</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 2"]May</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 2"]April</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD][/TD]
[TD]Transaction ID</SPAN>[/TD]
[TD]Department</SPAN>[/TD]
[TD][/TD]
[TD]Transaction ID</SPAN>[/TD]
[TD]Department</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]Accounts Payable</SPAN>[/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD]Payroll</SPAN>[/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3</SPAN>[/TD]
[TD]Treasury</SPAN>[/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]4</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]5</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD][/TD]
[TD]List:</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD][/TD]
[TD]Accounts Payable</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD][/TD]
[TD]Payroll</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15</SPAN>[/TD]
[TD][/TD]
[TD]Treasury</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 2"]Accounts Receivable</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 2"]Management Accounting</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=7></COLGROUP><TBODY>[TR]
[TD="colspan: 7"]I know transactions 1,2,3 are Accounts Payable, Payroll and Treasury respectfully and can therefore us a vlookup to return these values. However, for transactions 4 & 5, i wish to use a drop-down list from the list of departments (A12:A17)</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Hope this makes sense!</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Thanks for you help regardless..</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Aladin Akyurek]

Why not choose each either from a dropdown list or via a look up formula?

Mixing would be possible if

=IFERROR(VLOOKUP(TransctionID,Table,2,0),DataValidationCell)

I don't think this is maintenable.