Vlookup Argument ?

  • Thread starter Thread starter DZ
  • Start date Start date
D

DZ

I'm trying to understand a VLookup function that was function created by an
unknown person.

That part that is confusing to me is the 2nd argument which is

'ALMOS XS'!G20714:'ALMOS XS'!$A$1:$E$150

for the table range. I undrstand that the part before the ! is the sheet
'ALMOS XS' and I also understand that the part the follows is the cell range
.. What is confusing is the there seems to be a range referred to as 'ALMOS
XS'!$A$1:$E$150, so what is the part before that ('ALMOS XS'!G20714) for.

Why is ( 'ALMOS XS'!G20714 ) in there when I already have a range referred
to as
'ALMOS XS'!$A$1:$E$150

I checked and ( 'ALMOS XS'!G20714) is not a named range

Thanks for any help

Angelo
 
Hi Angelo,

Can you give the whole formula? Don't type it, copy it from the formula bar and paste it into your post

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm trying to understand a VLookup function that was function created by an
| unknown person.
|
| That part that is confusing to me is the 2nd argument which is
|
| 'ALMOS XS'!G20714:'ALMOS XS'!$A$1:$E$150
|
| for the table range. I undrstand that the part before the ! is the sheet
| 'ALMOS XS' and I also understand that the part the follows is the cell range
| . What is confusing is the there seems to be a range referred to as 'ALMOS
| XS'!$A$1:$E$150, so what is the part before that ('ALMOS XS'!G20714) for.
|
| Why is ( 'ALMOS XS'!G20714 ) in there when I already have a range referred
| to as
| 'ALMOS XS'!$A$1:$E$150
|
| I checked and ( 'ALMOS XS'!G20714) is not a named range
|
| Thanks for any help
|
| Angelo
|
|
|
|
|
|
 
I think the original writer of the formula just screwed up.

Try Edit|goto and paste in that range:
'ALMOS XS'!G20714:'ALMOS XS'!$A$1:$E$150

You'll see that A1:G20714 is selected.

So the question is what should it be replaced with:

'ALMOS XS'!$a$1:$G$20714
Or
'ALMOS XS'!$A$1:$E$150
 
=IF(ISNA(VLOOKUP(Z4,'ALMOS XS'!B25708:'ALMOS
XS'!$A$1:$E$150,3,FALSE)),0,VLOOKUP(Z4,'ALMOS XS'!B25708:'ALMOS
XS'!$A$1:$E$150,3,FALSE))

I understand what this fomula is doing.

ISNA checks VLookup for the existance of a value and the If statement
displays 0 if no value exists otherwise it display the VLookup value. No
problem there.

I am just confused by argument 2 for the VLookup as i mentioned in my first
post.

Many thanks for any help.

DZ
 
I think Dave's right. This makes no sense, although it's not flagged as an error

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|
| =IF(ISNA(VLOOKUP(Z4,'ALMOS XS'!B25708:'ALMOS
| XS'!$A$1:$E$150,3,FALSE)),0,VLOOKUP(Z4,'ALMOS XS'!B25708:'ALMOS
| XS'!$A$1:$E$150,3,FALSE))
|
| I understand what this fomula is doing.
|
| ISNA checks VLookup for the existance of a value and the If statement
| displays 0 if no value exists otherwise it display the VLookup value. No
| problem there.
|
| I am just confused by argument 2 for the VLookup as i mentioned in my first
| post.
|
| Many thanks for any help.
|
| DZ
 
One thing I m pretty sure of is that the cell range is $A$1:$E$150 because
the last row on sheet ALMOS is row 150
 
Back
Top