Vlookup, Match Column header

[irajeev]

Hi

I am trying to create some columns in a new excel sheet based on existing sheet. On the sheet DATA"Instance number" is unique. and the range for DATA is huge and dynamic. Some times it can go to more than 5000 lines.

I have an issue as some column numbers change on DATA Tab. So am trying to use Vlook and MATCH to get information on to QUOTE Tab from DATA Tab. But i am getting errors. Can you please help me with the formula

Regards
Raj

[Pete_UK]

You can put this formula in B9 of the quote sheet:

=VLOOKUP($A9,DATA!$G$32:$CC$40,MATCH(INDEX($C$22:$O$22,MATCH(B$8,$C$23:$O$23,0)),DATA!$G$31:$CC$31,0),0)

then copy across. You will need to change the $40 (in red above) to suit the amount of data you have in your real file.

If you have more data in column A of the Quote sheet, then you can copy the formula down as well.

Hope this helps.

Pete

[irajeev]

hi Pete

Thanks for this. But assume "INDEX" is not required. as the ones i highlighted in yellow is for reference that the values in quote tab column headers should fall in to values in data tab column headers. this will make the formula a bit complicated. I should have explained before.

ex : "TARGET CONTRACT NUMBER" from DATA Tab Values should fall in to "CONTRACT NUMBER" Quote Tab Column.

Regards
Rajeev