Lookup combined with Match formulas???
 

What I am trying to do is take one figure from sheet 'MMS report' which is D2
and then match that text and alpha field (it would be something like 122345E)
to a an exact match in column C from sheet 'EORI report' and then return a
field from further along that row (column 9)

The formula i have tried to enter is as follows can anyone spot what i am
doing wrong as this is not returning a correct result?

VLOOKUP('MMS Report'!D2,(MATCH('MMS Report'!D2,'EORI
report'!C2:C296,1)),9,FALSE)

I have tried this with changing the Vlookup to a Hlookup but with the rest
of the formula the same in case i was misunderstanding it.

Any guidance given would be much appreciated.

--
Jim

Lookup combined with Match formulas???
 

It kind of sounds like you want to match a header row value and a header column
value and return the value at that intersection.

If that's true, look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions03.html
Especially example 2


Jim wrote:

What I am trying to do is take one figure from sheet 'MMS report' which is D2
and then match that text and alpha field (it would be something like 122345E)
to a an exact match in column C from sheet 'EORI report' and then return a
field from further along that row (column 9)

The formula i have tried to enter is as follows can anyone spot what i am
doing wrong as this is not returning a correct result?

VLOOKUP('MMS Report'!D2,(MATCH('MMS Report'!D2,'EORI
report'!C2:C296,1)),9,FALSE)

I have tried this with changing the Vlookup to a Hlookup but with the rest
of the formula the same in case i was misunderstanding it.

Any guidance given would be much appreciated.

--
Jim

--

Dave Peterson

Lookup combined with Match formulas???
 

I'm not sure why you are using the MATCH formula, as the VLOOKUP function
with a final arguement of "false" already finds an exact match. This is what
I would go with:

=VLOOKUP('MMS Report'!D2,'EORI report'!$C$2:$I$296,7,FALSE)
or
=VLOOKUP('MMS Report'!D2,'EORI report'!$C$2:$K$296,9,FALSE)

depending on what you meant by 9th column.

If this doesn't work perhaps you could try to exlpain in more detail what it
is exactly you are trying to do?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jim" wrote:

What I am trying to do is take one figure from sheet 'MMS report' which is D2
and then match that text and alpha field (it would be something like 122345E)
to a an exact match in column C from sheet 'EORI report' and then return a
field from further along that row (column 9)

The formula i have tried to enter is as follows can anyone spot what i am
doing wrong as this is not returning a correct result?

VLOOKUP('MMS Report'!D2,(MATCH('MMS Report'!D2,'EORI
report'!C2:C296,1)),9,FALSE)

I have tried this with changing the Vlookup to a Hlookup but with the rest
of the formula the same in case i was misunderstanding it.

Any guidance given would be much appreciated.

--
Jim

Lookup combined with Match formulas???
 

Thanks, that 1st one worked a treat, i got confused over the MATCH formula
turns out it wasn't needed, no wonder i couldn't get it to work. Again thanks
for your assistance you've been a great help..
--
Jim


"Luke M" wrote:

I'm not sure why you are using the MATCH formula, as the VLOOKUP function
with a final arguement of "false" already finds an exact match. This is what
I would go with:

=VLOOKUP('MMS Report'!D2,'EORI report'!$C$2:$I$296,7,FALSE)
or
=VLOOKUP('MMS Report'!D2,'EORI report'!$C$2:$K$296,9,FALSE)

depending on what you meant by 9th column.

If this doesn't work perhaps you could try to exlpain in more detail what it
is exactly you are trying to do?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jim" wrote:

What I am trying to do is take one figure from sheet 'MMS report' which is D2
and then match that text and alpha field (it would be something like 122345E)
to a an exact match in column C from sheet 'EORI report' and then return a
field from further along that row (column 9)

The formula i have tried to enter is as follows can anyone spot what i am
doing wrong as this is not returning a correct result?

VLOOKUP('MMS Report'!D2,(MATCH('MMS Report'!D2,'EORI
report'!C2:C296,1)),9,FALSE)

I have tried this with changing the Vlookup to a Hlookup but with the rest
of the formula the same in case i was misunderstanding it.

Any guidance given would be much appreciated.

--
Jim