unknown formatting causing issues

[paquirl]

A colleague sent me a workbook that a customer gave to him. I am having issues with getting vlookups to work properly and it appears that there is some sort of automatic formatting or programming going on that changes stuff as I go. Is there a good way to grab the data and put it into a clean workbook without those issues? I have literally copied and put into a new workbook but that did not work out.

 

[dreid1011]

Copy into Notepad then into a new workbook possibly?

 

[paquirl]

ok maybe this is the right question, how do you make a vlookup not stop if there's a second instance in a list?

 

[paquirl]

00345	07660700345
00346	07660700346
00350	07660700350
00350	#N/A

 

[dreid1011]

ok maybe this is the right question, how do you make a vlookup not stop if there's a second instance in a list?

You want to return all results for the lookup value?

 

[Fluff]

how do you make a vlookup not stop if there's a second instance in a list?

You can't vlookup will always return the 1st matching value

 

[paquirl]

i dont understand why this workbook is jacking up my vlookup formula. i just saw what it's doing. When i double click the little square on bottom right, it normally populates the vlookup formula correctly to go down the column. But what it's doing in this workbook is changing the vlookup range for each cell as it goes down.

=VLOOKUP(B2,Sheet1!A1:B345,2,FALSE)
=VLOOKUP(B3,Sheet1!A2:B346,2,FALSE)
=VLOOKUP(B4,Sheet1!A3:B347,2,FALSE)

00345	07660700345
00346	07660700346
00350	07660700350

 

[Fluff]

You need to lock the rows like

=VLOOKUP(B2,Sheet1!A$1:B$345,2,FALSE)