merge data vlookup and find the error where in data base

[rahulshewale1]

hii sir,

I have big data receip sheet i want to lookup value from price sheet.i have used vlookup formula it is working fine but if i drag it till U65.Error msg show "operation required merge cell to be identically sized".
question 1) is it possible with out unmerge cell draging formula?
Question 2) i want find error ingredient name in error sheet.


Thanks
Rahul

 

[AVK]

in sheet remove all merge cell.

 

[rahulshewale1]

Thanks for reply .
But is it possible with out all merged cell.
And I want list whose item result is error .(na) in error name sheet.

Thanks
Rahul Shewale

 

[bosco_yip]

Ans. 1

A workaround way by using an unused range AH27:BM50:

1] Select A27:AF50 >> Format Painter , paste to AH27:BM50

2] Un-merge A27:AF50

3] Dragging your Vlookup formula appeared in X8 down to X64

4] Select AH27:BM50 >> Format Painter , paste back to A27:AF50

5] Delete Format in AH27:BM50

Ans. 2

In "Error Sheet" A2, formula copy down :

=IFERROR(INDEX(vlookup!E$8:E$64,AGGREGATE(15,6,(ROW(U$8:U$64)-ROW(U$8)+1)/ISERROR(vlookup!U$8:U$64)/(vlookup!E$8:E$64>0),ROWS($1:1))),"")

Regards
Bosco

 

[rahulshewale1]

hii bosco sir,

thanks for help i am learning new thing from you.question 2 is solve thanks a lots.
but question 1 :if excel have same A27:AF50 range data so on till A27:AF15000 so how can do with manually every time.

Thanks
rahul

 

[bosco_yip]

........if excel have same A27:AF50 range data so on till A27:AF15000 so how can do with manually every time..........rahul

Try,

Press F5 to display the Go To window and

in the Reference box enter : A27:AF15000 >> OK

then, follow the steps in post # 4.

Regards
Bosco

 

[rahulshewale1]

Dear Bosco sir,

i have try but i failed please find the attached sheet.sample data is small please check once.

Thanks
Rahul shewale

 

[bosco_yip]

Dear Bosco sir,
i have try but i failed please find the attached sheet.sample data is small please check once.
Thanks
Rahul shewale

Hi,

1] I think the copy down formula obstructed by the GIF pictures.

2] Try to hide all GIF pictures by :

Double click anyone GIF picture >> Picture Tool Format >> click Selection Pane >> click Hide All >> OK

This worked in my Excel 2016.

3] Then, continue the copy format, un-merge cells and copy down formula work... etc. as per post # 4

Regards
Bosco

 

[rahulshewale1]

dear bosco,
it's works thanks a lot............i had delete GIF picture using GO TO (F5)



Thanks
Rahul shewale.