search text within a cell and vlookup

[askapadia]

Hi

This is my first post.
Apologies if I am being dumb.

I just need a formula which can search in a table for "CSH" and do a lookup of the data in that row
I mean: I want to search for cell that contains text "CSH" in Column D and vlookup all the data in that row in another sheet

I have attached the excel sheet for your reference
Below is the screenshot as well
Untitled.png

Is there a formula which can do a vlookup accordingly?

Thank you

[FDibbins]

Hi and welcome to the forum

You have a number of entries with CSH in them. VLOOKUP() will only return the 1st "find" and will then stop looking. Also, VLOOKUP() need to search to the right of the criteria column

Do you want to return ALL entries with CSH in them? IF so, you can do this easily by applying filters and doing a text filter "contains" CSH. If needed, the resulting rows can be copy/pasted onto the new sheet

[askapadia]

Hi Ford

Thank you for a quick response

The excel sheet given is a bank statement where I want to segregate transactions like cash withdrawals, internet transfers, etc in individual sheets like Cash Withdrawal, Internet Transfer, etc.
I do this manually by copy pasting the transaction to the relevant sheets, however as I am maintaining this from a year, I am looking for something like this:
Copy paste the statement in the excel sheet
with the formulas I need, it should keep on adding the transaction to the relevant sheets with all the details

Is there a possibility for a formula using search and vlookup or hlookup formula?

[FDibbins]

see if the attached will get you headed in the right direction. I noticed that you have entries with "NABATM CSH 05th18:35PARAMOUNT CENTRE" and another 1 with "CSHF852561 Church Street Gy"
Is the CSH with a space either side the same as the 1 with no spaces?

If you dont want to pull in the 1 with no spaces, adjust the helper column formula to...
"CSH "

[askapadia]

Hi Ford

This is amazing ! Never knew this formula, Thank you !!

The formula you have entered in Sheet 1 would only work for "CSH ", is that correct?
Do I have add columns for each of the Searches like Internet Transfer, etc?

Is there anything which can formulate on a separate sheet, keeping the data sheet intact with no formulas?

Thank you

[FDibbins]

I have made a few adjustments to the file I sent you. It now contains a header on sheet 1, and I have added 2 more categories. If you change the header (eg CSH), it changes the data that gets counted. I have also added a few extra sheets, and set them up to pull from each of the 3 helper columns.

IF you need more sheets, 1st add/copy extra helper columns/formulas. Then add extra sheets, and copy the formulas across from 1 of the other sheets (eg CSH) - and adjust the 1st MATCH() to refer to the new hepler column. For instance, I changed...
=IFERROR(INDEX(Sheet1!$A:$D,MATCH(ROWS($A$1:A1),Sheet1!$H:$H,0),MATCH(CSH!A$1,Sheet1!$A$2:$D$2,0)),"")
to this, for the next column and sheet...
=IFERROR(INDEX(Sheet1!$A:$D,MATCH(ROWS($A$1:A1),Sheet1!$I:$I,0),MATCH(CSH!A$1,Sheet1!$A$2:$D$2,0)),"")

You can hide the helper columns, or move them way out of the way if you want

[azumi]

If you looking for partial lookup, like lookup for "CSH" or "INTERNET TRANSFER" or anything that avalaible on the lookup table and return value from Column D

try this

Azumi

[kvsrinivasamurthy]

Pl see attached file with Array Formula in Sheet2 A2 and dragged across.

[askapadia]

Hi Ford

Thank you for the resolution
You are absolutely amazing :-)

Thank you

[FDibbins]

aahh thank you for the kind words, always appreciated

Happy to help and looking forward to assisting you with your next question

[askapadia]

Hi Ford

Sorry to be a pain
Unfortunately using the same formula by increasing the cell references, I aint getting any results.
I am definitely doing something wrong, however don't know what?

I have attached the excel sheet where I was practicing. The search count works fine, but doesn't add the rows in Sheet2.
Can you also check what is wrong with the Cash Withdrawal sheet?

I know I may be asking too much, but can you write a sentence explaining what the formula does? this would help me to understand it better.
I am unsure if I have to open the thread, hence kept it closed.

Thank you

[askapadia]

Apologies ! I guess I had to open the thread for you to respond

[FDibbins]

See the attached. I have corrected CASH W/D sheet, not really sure what you wanted on sheet2, so I didnt touch that 1.

Also, you have 2013 (.xlsx), but you uploaded a .xls this time. Not sure why you selected to downgrade, but there are some functions I have used that are not compatible with .xls. If you still want to use that version (pre-2007), let me know and I will modify the formulas I used