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

[TABLE="width: 491"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Aldi[/TD]
[TD]Caltex[/TD]
[TD]Bunnings[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Coles[/TD]
[TD]Mobil[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Woolies[/TD]
[TD]Shell[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]IGA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]date[/TD]
[TD]Aldi[/TD]
[TD]$5[/TD]
[TD]$5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]date[/TD]
[TD]123 Coles[/TD]
[TD]$10[/TD]
[TD]$10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]date[/TD]
[TD]SHP Caltex[/TD]
[TD]$20[/TD]
[TD][/TD]
[TD]$20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]date[/TD]
[TD]Bunnings[/TD]
[TD]$40[/TD]
[TD][/TD]
[TD][/TD]
[TD]$40[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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