Sort issues with VLOOKUP

[mavsounds]

Hi, I'm having trouble sorting data and having columns return the correct data when I use VLOOKUP and IF statements across multiple worksheets.

A few details:

1. I have all worksheets formatted as a table because I will be connecting to Power BI.
2. Sheet 1 "DATABASE - MASTER" is where most of the data is entered.
3. On "DATABASE - MASTER", I have four columns that are using VLOOKUP to pull totals from "DATABASE - REVISED BIDS" worksheet. The lookup value is in Column D of "DATABASE - MASTER".
4. Here is my simple VLOOKUP formula =IF([@[Job Name]]="","",VLOOKUP([@[Job Name]],'DATABASE - REVISED BIDS'!A:T,20,FALSE))
4. When I sort by Column D of "DATABASE - MASTER", the columns with VLOOKUP pulling from "DATABASE - REVISED BIDS" are remaining static and not following Column D.
5. I have tried INDEX MATCH too and the same thing happens when I sort by Column D.
6. Here is the INDEX MATCH formula =IF([@[Job Name]]="","",INDEX('DATABASE - REVISED BIDS'!D:D,MATCH([@[Job Name]],'DATABASE - REVISED BIDS'!A:A,0)))
7. On my "DATABASE - REVISED BIDS", I am using this formula to return a value from "DATABASE - MASTER". =IF(tblData[@[PM/Estimator]]="","",tblData[@[Job Name]]). This is Column A. There are other columns on this worksheet that have data. The same thing happens when I try to sort Column A. The rest of the columns to do correctly sort.

Question:

What am I missing? What do I need to do so that the when I sort Column D on "DATABASE - MASTER" the columns with VLOOKUP from the other worksheet correctly sort?

 

[sandy666]

why you tryin' do something with worksheet formula if you can do the same with PowerQuery in PowerBI ?
IMHO, mixing worksheet functions with PowerQuery/DAX is not a good idea.