Index Formula Help

klatlap · May 29, 2011

i am using a formula similar to this to lookup but if the cell is blank it returns a zero how can i get it to return blank
=IF(INDEX($BS$15:$BS$350,MATCH($AG$4,$BS$15:$BS$350,0)+1,1)="","",INDEX($BS$15:$BS$350,MATCH($AG$4,$BS$15:$BS$350,0)+1,1))

Robert Mika · May 29, 2011

For curiosity
is this the same formula or similar?
Would it return 0 if the lookup value is 0?

klatlap · May 29, 2011

Just tested it and it would return the correct lookup, i even tried this as the match is always a number.

=IF(ISNUMBER(INDEX($BS$15:$BS$350,MATCH($AG$4,$BS$15:$BS$350,0)+3,1)),INDEX($BS$15:$BS$350,MATCH($AG$4,$BS$15:$BS$350,0)+3,1),"")

T. Valko · May 29, 2011

klatlap said:
Just tested it and it would return the correct lookup, i even tried this as the match is always a number.

=IF(ISNUMBER(INDEX($BS$15:$BS$350,MATCH($AG$4,$BS$15:$BS$350,0)+3,1)),INDEX($BS$15:$BS$350,MATCH($AG$4,$BS$15:$BS$350,0)+3,1),"")

I'm a bit confused about this.

Both of the formulas that were posted do what you want. Don't they?

klatlap · May 29, 2011

No,if the cell is blank it returns a zero i would rather it returned a blank if the cell does not contain data

Robert Mika · May 29, 2011

One thing I have got in mind woluld be formating but that must have been some kind of "Custom formatting".
Have you tried to play with that?

T. Valko · May 29, 2011

klatlap said:
No,if the cell is blank it returns a zero i would rather it returned a blank if the cell does not contain data

OK, I'm still confused!

See if this helps.

Book1

A

B

C

D

2

A

48

_

Lookup

3

B

13

_

D

4

C

79

_

5

D

_

6

E

54

_

7

F

56

_

8

G

29

_

9

H

68

_

Sheet1

Formula entered in D4 (that is returning a blank in this example):

=IF(INDEX(B2:B9,MATCH(D3,A2:A9,0))="","",INDEX(B2:B9,MATCH(D3,A2:A9,0)))

klatlap · May 29, 2011

Strange that works fine, must be my formatting or something else that it picks up.

klatlap · May 29, 2011

I think it is the offsets at the end, because that's what it has the problem with not the lookup or match, after it finds that, it needs to return the value in the cell indicated, in my formula it looks 1 cell down, i have 3 of these formulas and each looks up to 3 cells below and if one of these cells is empty that's when it returns zero

klatlap · May 29, 2011

No i just played with what you posted and it works the same if i add the offsets, must be formatting.

Index Formula Help

klatlap

Well-known Member

Excel Facts

Robert Mika

MrExcel MVP

klatlap

Well-known Member

T. Valko

Well-known Member

klatlap

Well-known Member

Robert Mika

MrExcel MVP

T. Valko

Well-known Member

klatlap

Well-known Member

klatlap

Well-known Member

klatlap

Well-known Member

Similar threads

Forum statistics

Share this page

Index Formula Help

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock