Excel Code Query

Cotta

Hi All,

The following code is functioning as required, however when the code meets the criteria it returns three different values "New Fee," "Source Fee" and "Old Fee" or otherwise it returns "Commission". Is there anyway to adjust this code so that instead of returning the variations of fee it simply returns "Fee"?

=IFERROR(VLOOKUP(F22,'[Fee Clients.xlsx]Sheet2'!$T:$AB,9,FALSE),"Commission")

Thanks

maas

I dont have excel to test this out on but try

=IF(IFERROR(VLOOKUP(F22,'[Fee Clients.xlsx]Sheet2'!$T:$AB,9,FALSE),"Commission"))="Commission","Commission","Fee")


That's just wrapping an IF statement around it, they'll be better ways of doing it

Cotta

Thanks Mass but this brings up an error message.

maas

I think I had 1 extra bracket, try this

=IF(IFERROR(VLOOKUP(F22,'[Fee Clients.xlsx]Sheet2'!$T:$AB,9,FALSE),"Commission")="Commission ","Commission","Fee")

Cotta

Excel corrects the code to the following, however this only returns a value of "FALSE" within all of the cells.

=IFERROR(VLOOKUP(F3,'[Sector Fee Clients.xlsx]Sheet2'!$T:$AB,9,FALSE),"Commission")

maas

My final effort

=IF(VLOOKUP(F22,'[Fee Clients.xlsx]Sheet2'!$T:$AB,9,FALSE),"Commission","Fee")

Cotta

maas wrote: »

My final effort

=IF(VLOOKUP(F22,'[Fee Clients.xlsx]Sheet2'!$T:$AB,9,FALSE),"Commission","Fee")

That looks close, it's returning an #N/A, if an "IFERROR" statement can be included it would be sorted.

maas

Actually I havent given up yet. Im just curious why the IF statement doesnt work.

In another cell on the spreadsheet type this

=IF(D6="Commission","Commission","Fee")

And replace D6 with the cell that the formula is in. Does this new cell give the desired outcome.

Cotta

maas wrote: »

Actually I havent given up yet. Im just curious why the IF statement doesnt work.

In another cell on the spreadsheet type this

=IF(D6="Commission","Commission","Fee")

And replace D6 with the cell that the formula is in. Does this new cell give the desired outcome.

Perfect little workaround, thank you very much.

rmg1

Try this (untested):-
=if(iserror(match(F22,'[Fee Clients.xlsx]Sheet2'!$T:$T,0)),"Commission","Fee")

maas

We should be able to get that all inside 1 cell. Try rmg1's formula.

If that doesnt work someone else should be able to get it all inside 1 cell.