Index Match Query returning incorrect value

[trish123]

=IFERROR(VLOOKUP(B18,Data!$A$1:$U$289,MATCH($C$16,Data!$A$1:$U$1,1)),"")

The above is the query - the issue is that it is returning the incorrect information. The information that is being returned is being pulled is from another sheet where there is a data validation list, but somehow it is not returning what has been chosen, but another item on the list.

When i drag this query down to other cells it only sees the same item on the list?

Anyone any ideas ??

 

[AlanY]

will you be able to post your sheet here by taking out the sensitive info?

 

[trish123]

I can only upload an image it seems

 

[AlanY]

I can only upload an image it seems

there are a few ways to post excel sheet here, have a look here

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

 

[trish123]

Permissions 2.xlsm
	B	C	D	E	F	G	H	I
2
3	Invoice
4
5	Customer Information				Invoice Information
6
7	Name				Date	21/09/2020		e
8	Address				Invoice Number	2020-1
9					Payment Required by Date	20/11/2020
10
11
12	Site(s)
13
14
15
16	Event Number	Event being requested	Facility Fee	Vat %	VAT amount	Total
17	E-3	Filming - Film
18
19
20
Invoice

Cell Formulas
Range	Formula
G7	G7	=TODAY()
G8	G8	=IF('Invoice Tracker'!A2="",YEAR(TODAY())&"-1",YEAR(TODAY())&"-"&MID(OFFSET('Invoice Tracker'!A1,COUNTA('Invoice Tracker'!A:A)-1,0),6,9999)+1)
G9	G9	=G7+60
B16	B16	=Data!B1
F17	F17	=IF(E17="","",D17*E17%)
G17	G17	=IF(E17="","",D17+F17)
C16	C16	=Data!H1
C17	C17	=VLOOKUP(Invoice!B17,Data!A1:U1584,MATCH(Invoice!C16,Data!A1:U1,0))
C18:C20	C18	=IFERROR(VLOOKUP(B18,Data!$A$1:$U$289,MATCH($C$16,Data!$A$1:$U$1,1)),"")

Named Ranges
Name	Refers To	Cells
Data!_FilterDatabase	=Data!$A$1:$U$1	C17:C20

Cells with Data Validation
Cell	Allow	Criteria
C12:C13	List	=Dropdown!#REF!

 

[trish123]

do you have all you need there Alan

 

[AlanY]

do you have all you need there Alan

would you also show us the range Data!$A$1:$U$289 please

 

[trish123]

Permissions 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Staff Member Assigned	Event Number	Depot	Site	IF NATIONAL MONUMENT IS NOT LISTED - Only	Date Request Received	Event Organisers Details	Event being requested	Event Details - If dates are not known enter details here	Start Date of Event	End Date of Event	Letter of Permission Date Issued	Indemnities Received and Filed
2	Breda	E-1	Trim				Joe Wicks	Filming - Educational	Filming	03/07/2020	03/07/2020
3	Breda	E-2	Trim				John Doe	Filming - Film	Choir Performance	17/07/2020	21/07/2020
4
5
6
Data

Cell Formulas
Range	Formula
B2	B2	=IF(A2="","",IF('Invoice Tracker'!D2="","E-1","E-"&MID(OFFSET('Invoice Tracker'!D1,COUNTA('Invoice Tracker'!D:D)-1,0),3,9999)+1))
B3	B3	=IF(A3="","","E-"&MID(B2,3,9999)+1)

Cells with Data Validation
Cell	Allow	Criteria
M2:M6	List	=#REF!
C3:C6	List	=SIte
A2:A6	List	=Dropdown!$D$2:$D$4
H2:H3	List	=Dropdown!$B$2:$B$37
C2	List	=Dropdown!$A$2:$A$4
D2	List	=Dropdown!#REF!

 

[AlanY]

the problem of the vlookup is to look up column 1 for matches (i.e. Col A), and you try to match the second column B (E-1, E-2 etc)

change C17 to

=VLOOKUP(Invoice!B17,Data!$B$1:$U$1584,MATCH(Invoice!C$16,Data!$B$1:$U$1,0),0)

should work

 

[AlanY]

the index-match approach is more flexible as you can match any columns or rows, such as

=INDEX(Data!$A:$U,MATCH(Invoice!B17,Data!$B:$B),MATCH(Invoice!C$16,Data!$1:$1,0))

 

[trish123]

Perfect AlanY thank you for all the help, by any chance could you help with the button to save the invoice to a tracker?