Nested Isnumber & search

[ken2601]

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

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>

 

[steve the fish]

Here is one way:

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

 

[ken2601]

Thank you Steve, works perfectly