Vlookup and match

[mahershams]

Dear Excel Forum

Please help me to sort out some data from "all data" sheet to next sheet from vertical column to horizontal row wise. I have provided some manual data in 176 row for an example.

Thanks in advance.

Regards
Shams

[hemesh]

can you please clarify what you are trying to achieve

[mahershams]

Dear Hamesh

Thanks for reply and sorry for the confusion. I would like to convert the F column (vertical column) in "all data file" sheet to horizontal (row wise) in "sorting of data sheet". For an example, in row 79 I have manually enter the values in G79 cell and onwards, which I am looking for from "all datasheet" file.

Please let me know if it makes sense to you.

Regards
Shams

[Fletch74]

Hi Shams,

Try this formula;

Please Login or Register  to view this content.

Note: This is an array formula, thus ensure you use Control+Shift+Enter after finishing the formula, not just Enter

[mahershams]

Dear Fletch74

Thanks for your help. But I have no idea why the formula provide me N/A for all the data when I drag it down. I have attached it for your look.

Thanks
Shams

[FDibbins]

Did you use CTRL SHIFT Enter to enter that, and not just Enter?

[FDibbins]

Looking at that formula again, I would change it to this...
=INDEX('All data'!$F$1:$F$1000,MATCH('sorting of data'!$C79&G$1,'All data'!$A$1:$A$1000&'All data'!$B$1:$B$1000,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

You should avoid using entire column references in ARRAY formulas (thats what that is by the way), as they tend to become resource-hogs and slow things down

[Fletch74]

Actually the reason for it not working is that there is no Data for those rows to Match (ie. Cell G80 - FY 2001, GVKEY 1076 is not in the "All Data" worksheet

I would use Ford's formula, but with an IFERROR around it as that will remove the N/A's from your sheet.

Please Login or Register  to view this content.

Again remember the CNTRL+SHIFT+ENTER

[FDibbins]

Good addition, Fletch

[mahershams]

Dear all

Thank you very much for excellent support.

You guys are great.

Thanks
Shams

[hemesh]

Hello Shams, you need to give the rep points to those who has provided you the solutions i.e. TO Fletch and Fdibbins not to me as I have asked you what you wanted to do and couldn't respond you back.

Rep points are for deserving people. I don't deserve anything here.

[FDibbins]

Happy to help and thanks for the fredback