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]

you need to change 1 to 0 for an exact match

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

 

[trish123]

AlanY thank you for your response on this, but unfortunately that does not work -

Cell B18 is where the user enters the Event ID which will be corresponding to that Event ID from the Data Sheet, based on this ID the corresponding event from the Data sheet will be returned. This event in the data sheet is a drop down list. However my query is not returning the chosen event from that line that would be associated with the event ID. Any ideas

 

[AlanY]

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

Data!$A$1:$U$289 is the lookup table,
MATCH($C$16,Data!$A$1:$U$1,0) use C16 to locate the Column for output

this is a cut down version of yours input/output, is this what you're after

Book1
	A	B	C	D	E	F
1			C	D	E	F
2	2		1	10	100	1000
3	D		2	20	200	2000
4	20		4	40	400	4000
5			8	80	800	8000
6			16	160	1600	16000
Sheet1

Cell Formulas
Range	Formula
C3:F6	C3	=2*C2
A4	A4	=VLOOKUP(A2,C1:F6,MATCH(A3,C1:F1,0))

 

[trish123]

Yes i think so, However data whiten column D has a data validation listing

 

[AlanY]

Col D now has data validation to Col H; I suppose there is something else you needed

Book1
	A	B	C	D	E	F	G	H
1			C	D	E	F
2	2		1	10	100	1000		10
3	D		2	40	200	2000		20
4	40		4	40	400	4000		40
5			8	80	800	8000		80
6			16	160	1600	16000		160
Sheet1

Cell Formulas
Range	Formula
A4	A4	=VLOOKUP(A2,C1:F6,MATCH(A3,C1:F1,0))

Cells with Data Validation
Cell	Allow	Criteria
D2:D6	List	=$H$2:$H$6

 

[trish123]

yep thats what i am looking for

 

[AlanY]

are you getting the right results you after?

 

[trish123]

Sorry about this AlanY am i to incorporate the allow part into the query?

 

[trish123]

=VLOOKUP(Invoice!B17,Data!A1:U1584,MATCH(Invoice!C16,Data!A1:U1,0))

So this is the Query now, but still no go, on the basis of the above illustration, ei am looking of the return of 40, but 80 is being presented

HELP