Basically i've been trying to fix this excel sheet for my boss at work, for some reason the following formula: "=IF(ISNA(VLOOKUP($N$4,WData!$C$1:$JS$57,MATCH($A57&C$13,WData!$C$1:$CK$1,0),TRUE)),0,VLOOKUP($N$4,WData!$C$1:$JS$57,MATCH($A57&C$13,WData!$C$1:$CK$1,0),TRUE))"

won't work, there are around 8 versions of this same formula the only difference between them all is a cell number.

I've gone through the entire worksheet and can't for the life of me find out what is wrong, if someone could help it would be much appreciated.regards, Joe

Imagine how much more help we could be if you told us "won't work" means. Keep in mind that we can't see your spreadsheet from where we're sitting, so we are at a bit of a disadvantage.

Sorry yeah, didn't think to put any of that stuff. Basically the formula is supposed to show the numbers of iphone app downloads for our company's app, the data is stored in one sheet (WData) and is displayed on another sheet (home screen). However, for some reason nothing is displayed on the home screen, all we get is a dash.

Hi, Three points:

1.

The most likely reason for returning different data to that expected is the use of TRUE in the VLOOKUP function. If the data in column C on WData is not in strict alpha-numeric order, VLOOKUP returns the next largest value that is less than lookup_value. As it expects the data to be sorted in order, if it isn't you get a different row selected.Try VLOOKUP($N$4,WData!$C$1:$JS$57,1,TRUE)

and see if it returns the same value as is in N4

If not, change TRUE to FALSE (no quotes) and see if you get the right value.Change the formula to this:

=IF(ISNA(VLOOKUP($N$4,WData!$C$1:$JS$57,MATCH($A57&C$13,WData!$C$1:$CK$1,0),FALSE)),0,VLOOKUP($N$4,WData!$C$1:$JS$57, MATCH($A57&C$13,WData!$C$1:$CK$1,0),FALSE))2.

The range for the Match function, which is used to determine which row to return a value from does not match the range used by VLOOKUP.

VLOOKUP will work if the column to return data from is between columns C and CK, but not if the text from $A57&C$13 is in a column between CK and JS.Without knowing what your data is like, I don't know which range is correct.

This corrects the formula for the larger range:

=IF(ISNA(VLOOKUP($N$4,WData!$C$1:$JS$57,MATCH($A57&C$13,WData!$C$1:$JS$1,0),FALSE)),0,VLOOKUP($N$4,WData!$C$1:$JS$57, MATCH($A57&C$13,WData!$C$1:$JS$1,0),FALSE))3.

I also note that

$A57&C$13

uses mixed absolute and relative addressing. This is likely what is intended, but I can't be sure.

Dragging your formula down a row will result in this:

$A58&C$13

Dragging your formula right one column will result in this:

$A57&D$13Check that this is what is wanted when you drag and extend the formula to other cells.

Regards

Hey thanks, it seems that answer number two worked. The formula is now returning the correct data. Thanks a bunch! great help!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History