Vlookup reading from wrong row when table array data alters

Question

I currently have a problem with my vlookup formula on office 2010 that reads off a pivot table. This Pivot table alters everyday from my companies EDI data and needs to be extracted into another sheet. My Vlookup formula which is =VLOOKUP($D270,'G:\LOGISTICS\BPCS DOWNLOADS[LANDROVERDOWNLOAD JIT  MASTER.xlsx]PIVOT'!$93:$200,HLOOKUP(BE$4,'G:\LOGISTICS\BPCS DOWNLOADS[LANDROVERDOWNLOAD JIT  MASTER.xlsx]PIVOT'!$95:$200,106,FALSE),FALSE) works succesfully most of the time.

However occasionally on some days it reads off the wrong row from the table array after being refreshed which is really annoying as this can be mistakenly taken as the correct value. If I highlight the formula from another cell and drag it across it alters itself to the correct value, so my formula is still correct and reading off the right information when prompted, but it does not seem to be refreshing correctly. I think this only happens when a row from the table array is removed, but is there anyway I can make sure this does not happen? I can't imagine I am the only one with this problem

Can anybody help thanks

Matt

Answer 1

Hi,

If you expect data will be added beyond row 12, then convert the range into a Table.  Select B3:D12 and press Ctrl+T (ensure that the My Table has headers box is checked).

Now when you add data beyond row 12, the range in the SUMIF() function will keep expanding.

If my response solved your problem, please ,ark it as Answer.

Answer 2

Hi,

Try this

1. Suppose you data (pasted above) is in range B4:D12 (headings are in B3:D3)
2. In range B18:C18, type LR6459C and 20130307 respectively
3. In cell D18, enter this formula

=SUMIFS($D$4:$D$12,$B$4:$B$12,B18,$C$4:$C$12,C18)

Hope this helps.

Answer 3

Hi Ashish

Thank you very much, this works fantastically well. I just hope it refreshes better than the vlookup formula did.

Answer 4

Ok I'm not sure how to use these functions in this context. What I want is to look up values in column 1 and 2 eg "LR6459C" & "20130307" which will return me the value in column 3 of 55 into my cell. How would I do this? I hear there is an index and match function but no idea how to use this 

KHITEM	KDDATE	KDRQTY
LR6459C	20130307	55
LR6459C	20130308	55
LR6459C	20130311	55
LR6459C	20130312	66
LR6459C	20130313	55
LR6459C	20130314	55
LR6459C	20130315	44
LR6459C	20130318	66

LR6460C   20130307      107

Answer 5

Hi,

It will be best to fetch data from the source data sheet (rather than from the Pivot Table).  Depending upon your exact question, you may have to use either the SUMIF() or SUMPRODUCT() function.