# Nested Isnumber & search

#### ken2601

##### New Member
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.

ta

Ken

 A B C D E F 1 Aldi Caltex Bunnings 2 Coles Mobil 3 Woolies Shell 4 IGA 5 6 date Aldi \$5 \$5 7 date 123 Coles \$10 \$10 8 date SHP Caltex \$20 \$20 9 date Bunnings \$40 \$40

<tbody>
</tbody>

<tbody>
</tbody>

### 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
Here is one way:

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

#### ken2601

##### New Member
Thank you Steve, works perfectly

Replies
5
Views
69
Replies
0
Views
48
Replies
3
Views
29
Replies
3
Views
112
Replies
5
Views
31