Vlookup Errors

[hardeep.kanwar]

Hi! Experts

I have a table
for eg See the Attached

In Sheet " Master" column A contains CustCode. In Sheet" Vlookup" Column A contains another CustCode.
when i give a vlookup in a different sheet to check for PropNo. in column B . The first cell works fine, but for the second cell it returns the Same value

Is there a way to get round this

Thanks in advance

Hardeep Kanwar

[zbor]

Try to see this (need to add helper column)

Vlookup%20Error(1).xls

You can't use vlookup function because vlookup look only first value. I try with INDEX function.

[hardeep.kanwar]

Try to see this (need to add helper column)

Attachment 48202

You can't use vlookup function because vlookup look only first value. I try with INDEX function.

Wow!!!!!!!!!!!!!!!!!!!!!!

I have never Seen the combination of Vlookup and Index/Match.

Perfect!

Appriciate zbor

Thanks for Saving Lots of Time.

[zbor]

And this isn't combination It's index+match instead of vlookup

(vlookup that appear in formula is name of your sheet)

btw, you can make with single cell:

=INDEX(Master!A:B;MATCH(Vlookup!A2;Master!A:A;0)-1+(COUNTIF($A$2:A2;A2));2)

(just joined)

[hardeep.kanwar]

And this isn't combination It's index+match instead of vlookup

(vlookup that appear in formula is name of your sheet)

btw, you can make with single cell:

=INDEX(Master!A:B;MATCH(Vlookup!A2;Master!A:A;0)-1+(COUNTIF($A$2:A2;A2));2)

(just joined)

Oops! I notice know.

Second one is also Perfect

Thanks zbor

[hardeep.kanwar]

And this isn't combination It's index+match instead of vlookup

(vlookup that appear in formula is name of your sheet)

btw, you can make with single cell:

=INDEX(Master!A:B;MATCH(Vlookup!A2;Master!A:A;0)-1+(COUNTIF($A$2:A2;A2));2)

(just joined)

Sorry to Disturb You again

What if the both Data is in 1 Sheet. then, what is condition in vlookup (Vlookup!A2)

I Try VlookupA2 and Also Vlookup(A2) but, both not Working

regards

Hardeep kanwar

[zbor]

Vlookup!A2 is name of the sheet.. If you have everything on one sheet then you won't need names of sheet.

For example: =A2 will look in same sheet. =Vlookup!A2 will look on Vlookup sheet, cell A2

However, I don't think you'll made only by renaming sheets in formula. You'll need to upload another example

[hardeep.kanwar]

Vlookup!A2 is name of the sheet.. If you have everything on one sheet then you won't need names of sheet.

For example: =A2 will look in same sheet. =Vlookup!A2 will look on Vlookup sheet, cell A2

However, I don't think you'll made only by renaming sheets in formula. You'll need to upload another example

I got it

Thanks zbor

Hope to hear you again

[zbor]

anytime...

[hardeep.kanwar]

anytime...

Hey zbor sorry to Disturb you Again.

I have a same Sheet With more Data in Master Sheet.

Your Formula Works Gr8 for Me.

Now i want same formulas in From B to M.

How can i Modifiy your Formula it Fill the Data in Whole sheet i.e. B2:M771

I mean to say don't want to Entered Formula for Each Column.

Thanks in Advance

Hardeep Kanwar

[zbor]

try to write this in C2:

=VLOOKUP($B2;Master!$B$2:$M$1923;COLUMN(B2);FALSE)

(replace ; with , if needed) and extend all way right and down

[hardeep.kanwar]

try to write this in C2:

=VLOOKUP($B2;Master!$B$2:$M$1923;COLUMN(B2);FALSE)

(replace ; with , if needed) and extend all way right and down

Good to Hear you again

That mean i have to Entered 2 Formulas to get These Data from Master Sheet.

1st in Column B and 2nd in Column C.

Hardeep kanwar

[zbor]

Yes, althought I don't know what's point of it since you get at the end same tables in Vlookup and Master

[hardeep.kanwar]

Yes, althought I don't know what's point of it since you get at the end same tables in Vlookup and Master

Ok,

I was thinking in your formula to give some Reference or Absolute Reference to Drag your Formula from B2:M2 and Then, Drag to down.

Actually, Master sheet is software Generated Sheet.

And Vlookup!Sheet is Received from Client. On Daliy Basis we get this data.

Sometimes CustCode (Column A) wise and Sometimes ZoneWise( columnG).

Sometimes for a Particular Zone i.e. NZ or WZ or SZ or CZ or EZ and sometimes Mix.

Basically the main Requirement is the Last 6 columns ( Column H:M)

Any Way thanks zbor

Regards

Hardeep Kanwar