vlookup query

[shanekenny]

Hi

Hope someone can help with this query I am struggling with!

I have two sets of data with no unique identifier to match them and clash them. So I am concatenating a number of fields (lets say between 2 & 4) to create a unique identifier in the master set of data and also the second set. See example of identifier below.

I then want to lookup the matching value in the second set of data, bring it back to the master.

I think I need a Vlookup with a nested IF as the concatenated identifier field is far in excess of 255 characters and I am getting a lot of "#VALUE" returns on the lookup.

Additionally I need to take "#NA" values and assign them a value of "NOT FOUND". Values that are successfully found would be assigned the value of "GREEN".

Hope someone can help!

Master	Secondary data
servername1violationA	servername1violationA
servername2violationB	servername2violationB
servername3violationC	servername3violationC
servername4violationD	servername4violationD
	servernameZviolationX

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[peterhinton]

not quite sure what you require from a lookup/If function,

you can however use =IFERROR(yourformulahere,"NOT FOUND") to replace any errors with NOT FOUND

 

[shanekenny]

not quite sure what you require from a lookup/If function,

you can however use =IFERROR(yourformulahere,"NOT FOUND") to replace any errors with NOT FOUND

thanks Pete

that's fine, but my formula is returning the value I am looking for when it does find it, I want to replace that value with "GREEN"

"NOT FOUND" then applies to everything else

=IFERROR(VLOOKUP([@[UID B]], Monthly!A:B, 1), "NOT FOUND")

 

[AliGW]

Try this:

=IF(ISNA(VLOOKUP([@[UID B]], Monthly!A:B, 1)), "NOT FOUND","GREEN")

 

[shanekenny]

I think that's the one I am after ALIGW, many thanks, until the requirement changes!