Nested Isnumber & search

ken2601

New Member
Joined
May 16, 2010
Messages
2
Hi all,

I enjoy reading the various posts and have learnt so much over the years, thank you to all those that contribute

I am endeavouring to build a spreadsheet where I can import credit card transactions via csv to excel
The list could be say 200 items long
They are imported into columns A, B and C and represent say 30-50 different providers
I then wish to list each provider/supplier in columns D, E and F (the actual number of columns could be approx 20 columns across, each column would represent a purpose ie Column D would be groceries, column E would be petrol. This will then group each classification of spending into a column for totalling
Whilst I have only listed 4 in the below example, the list will be much longer, say 15 covering D1 to D15

In cell D6,(and D7,D8,D9,E6,E7,E8 etc) I am trying to workout a formula where it will use the range listed in D1 to D4 (up to say 15 items), then search column B for a match, then obtain the value from the adjacent column C and insert that value into D6

The actual name in column B will longer, but it will contain the shop name as listed in D 1 - D4 somewhere within the narration

I have achieved this doing nested isnumber(search formulas, but after the sixth nesting, it states that this is the maximum number of nestings allowed, which restricts me to 6 search items at the top of each column, I seek the option for say 15 in each column

Have searched for other types of formulas, but cannot locate anything that I can make work.

Can somebody please assist

ta

Ken

ABCDEF
1AldiCaltexBunnings
2ColesMobil
3WooliesShell
4IGA
5
6dateAldi$5$5
7date123 Coles$10$10
8dateSHP Caltex$20$20
9dateBunnings$40$40

<tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Here is one way:

=IF(OR(INDEX((D$1:D$4<>"")*(ISNUMBER(SEARCH(D$1:D$4,$B6))),0)),$C6,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,049
Messages
5,526,488
Members
409,703
Latest member
nbkqsj7

This Week's Hot Topics

Top