VLOOKUP Results stopping after the second

[cheezboycheez]

I've created an invoice form which uses the Invoice number to return the results, there are multiples of the same number, say there could be 4 different lines using invoice number 72. So far I've created my vlookup fields as (First line)

=VLOOKUP(E4,'Invoice Sheet'!A:M,3,0)

and then following, so it doesn't repeat the same line/ and doesn't display #n/a

=IF(VLOOKUP(E4,'Invoice Sheet'!A:M,3,1)<=A15,"",VLOOKUP(E4,'Invoice Sheet'!A:M,3,1))

This seems to work fine, except when it gets to line 3

=IF(VLOOKUP(E4,'Invoice Sheet'!A:M,3,2)<=A16,"",VLOOKUP(E4,'Invoice Sheet'!A:M,3,2))

Which returns no data...... when it should be.
Any ideas?

Andy

 

[Domski]

Hi & welcome,

2 isn't a valid setting for the last part of the VLOOKUP function. You can only use 0 (or FALSE) for an exact match or 1 (or TRUE) for a nearest match.

Dom

 

[cheezboycheez]

Good Point Domski
Although is there any other way to get it to show more than the 2 results?

 

[AdamL]

I've created an invoice form which uses the Invoice number to return the results, there are multiples of the same number, say there could be 4 different lines using invoice number 72. So far I've created my vlookup fields as (First line)

=VLOOKUP(E4,'Invoice Sheet'!A:M,3,0)

and then following, so it doesn't repeat the same line/ and doesn't display #n/a

=IF(VLOOKUP(E4,'Invoice Sheet'!A:M,3,1)<=A15,"",VLOOKUP(E4,'Invoice Sheet'!A:M,3,1))

This seems to work fine, except when it gets to line 3

=IF(VLOOKUP(E4,'Invoice Sheet'!A:M,3,2)<=A16,"",VLOOKUP(E4,'Invoice Sheet'!A:M,3,2))

Which returns no data...... when it should be.
Any ideas?

Andy

A question and an observation:

Are you wanting to return the value in the third column of only the first instance of the invoice number, or every instance?

And just to clarify, the fourth argument in a VLOOKUP is not an "offset" type argument, it just stipulates whether an exact match (FALSE) or an approximate match (TRUE) is required. FALSE can be represented by 0, and TRUE can be represented by 1, 2 or 5563.

edit: beaten

 

[Domski]

It is possible, if you do a search for 'lookup multiple results' you should see quite a few posts along the same lines.

Dom

 

[cheezboycheez]

Found my stuff in VLOOKUPNTH thanks guys!!