if lookup doesn't work...?

[Aberdham]

Hi Excel Gurus,

I have a list of invoice number such as:

12RD000252
15RC000564
18MD000548
18MC004512

type:
RD deposit
RC credit
MD withdraw
MC credit

I would like to lookup the respective invoices whether it is a deposit or a credit or a withdraw..

I have the respective formula:

=IF(VLOOKUP(D1692;D$1692:D$2157;1;FALSE)="*"&AB1692&"*";"Deposit";"withdraw")

but it doesn't seem to look up the things i want.

could anyone help me with this ?

your answers will be greatly appreciated!

Thanks!

 

[Peter_SSs]

Does this, copied down, do what you want?
If not, can you give further details including where your data is located, where the formula should go & further examples of data and expected results that might help clarify what you have and what you want.

Excel Workbook
	A	B
1	12RD000252	deposit
2	15RC000564	credit
3	18MD000548	withdraw
4	18MC004512	credit
Type

 

[Peter_SSs]

.. or possibly this if it makes more sense to you.

Excel Workbook
	A	B
1	12RD000252	deposit
2	15RC000564	credit
3	18MD000548	withdraw
4	18MC004512	credit
Type

 

[Aberdham]

ah this works perfectly !

thank you so much !

what if i want to make a specification on RC and MC (credit lease & credit purchase)

 

[Peter_SSs]

what if i want to make a specification on RC and MC (credit lease & credit purchase)

One way is to make this slight adjustment to the post #2 formula
=LOOKUP(MID(A1,3,2),{"MC","MD","RC","RD"},{"credit purchase","withdraw","credit lease","deposit"})

 

[Aberdham]

ah perfect !! I was still struggling with the if function...

or does it also work with IF or if error function?

Thank you so much for your help Peter!!

 

[Aberdham]

One way is to make this slight adjustment to the post #2 formula
=LOOKUP(MID(A1,3,2),{"MC","MD","RC","RD"},{"credit purchase","withdraw","credit lease","deposit"})

Hi Peter, sorry to bother you again, I just found out the list has also invoices Formatted as 15DD000526 and 15DC000653

I included that in the range and the description, however it Returns to #N/A

what would be the reason?

 

[Charlie45]

I believe the LookUp value should be in Alphabetical order,


=LOOKUP(MID(A1,3,2),{"DC","DD","MC","MD","RC","RD"},{"DC","DD","credit purchase","withdraw","credit lease","deposit"})

 

[Peter_SSs]

what would be the reason?

Charlie45 has the answer to that. I have highlighted the relevant section for clarity. The items in that section must be in alphabetical order.

I believe the LookUp value should be in Alphabetical order,


=LOOKUP(MID(A1,3,2),{"DC","DD","MC","MD","RC","RD"},{"DC","DD","credit purchase","withdraw","credit lease","deposit"})

If you are still having difficulty, post the formula that you are using and some sample data and the expected results that your formula is not returning.


In answer to your other question, you could do this with IF functions, but especially now that there are 6 options, you would be getting quite long formula with lots of nested IF functions.