Lookup Help

[dxz156]

I really need some help. I have a feeling that this is easy but I am too stupid to figure it out. I have a whole bunch of data that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1124[/TD]
[TD]fish[/TD]
[/TR]
[TR]
[TD]1124[/TD]
[TD]dog[/TD]
[/TR]
[TR]
[TD]1124[/TD]
[TD]bird[/TD]
[/TR]
[TR]
[TD]1568[/TD]
[TD]fish[/TD]
[/TR]
[TR]
[TD]1568[/TD]
[TD]bird[/TD]
[/TR]
[TR]
[TD]3265[/TD]
[TD]dog[/TD]
[/TR]
[TR]
[TD]3265[/TD]
[TD]bird[/TD]
[/TR]
[TR]
[TD]7895[/TD]
[TD]fish[/TD]
[/TR]
</tbody>[/TABLE]

I need to get it to look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]F1[/TD]
[TD]Fish[/TD]
[TD]Dog[/TD]
[TD]Bird[/TD]
[/TR]
[TR]
[TD]1124[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]1568[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]3265[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]7895[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I tried the formula =IF((VLOOKUP($F2;a1:b8;2;FALSE))="fish";"x";"") and it works but when I do the same thing for the "dog" column it does not. I think it is only looking up to the first match and not going to where it matches again.

Any help would be great. Please remember that I am an extreme novice at this

Thanks!

 

[StefanVL]

In G2 enter the following formula:

=if(isnumber(match("x";if($a$2:$a$16=$F2;if($b$2:$b$16=g$1;"x"));0));"x";"")

enter the formula with control + shift + enter

 

[dxz156]

Stefan, you are AWESOME!!

 

[StefanVL]

Glad i could help!

 

[dxz156]

Stefan,

You helped me out back in February with the below formula (which was awesome by the way) but I have run into a new problem along the same lines.

In G2 enter the following formula:

=if(isnumber(match("x";if($a$2:$a$16=$F2;if($b$2:$b$16=g$1;"x"));0));"x";"")

enter the formula with control + shift + enter

I am using your formula as this =IF(ISNUMBER(MATCH("x";IF('Report 1'!J5:J65536=G5;IF('Report 1'!K5:K65536=I4;"X"));0));"x";"") but i need it to look on two different sheets. So it would if 'Report 1' J range=g5 and 'Report 1' K range=I4 then "X" OR If 'Report 2' J range=g5 and 'Report 2' K range=I4 then "X"

Basically I have too many rows to fit on one sheet so I need it to look at both to return the X

I hope that made sense!

Thanks!!!

 

[StefanVL]

Stefan,

You helped me out back in February with the below formula (which was awesome by the way) but I have run into a new problem along the same lines.



I am using your formula as this =IF(ISNUMBER(MATCH("x";IF('Report 1'!J5:J65536=G5;IF('Report 1'!K5:K65536=I4;"X"));0));"x";"") but i need it to look on two different sheets. So it would if 'Report 1' J range=g5 and 'Report 1' K range=I4 then "X" OR If 'Report 2' J range=g5 and 'Report 2' K range=I4 then "X"

Basically I have too many rows to fit on one sheet so I need it to look at both to return the X

I hope that made sense!

Thanks!!!

Try something like this:

=IF(ISNUMBER(MATCH("x",IF('Report 1'!$A$2:$A$16&'Report 1'!$B$2:$B$16=$F2&G$1,"x",IF('Report 2'!$A$2:$A$9&'Report 2'!$B$2:$B$9='Report 1'!$F2&'Report 1'!G$1,"x")),0)),"x","")

 

[StefanVL]

Do note that I used a comma instead of a semicolon in this formula.