retrieve value from column based on separate column

[dhx10000]

I have a worksheet that contains data similar to this:

A B
--------- -------------
578 22395
578 22395
625 37553
703 37553
811 49995
811 49995
823 49995

-----------------------------------------------------
I have another sheet where I am trying to do this:

G H
----- --------
578 22395
625 37553
703 37553
811 49995
823 49995

I already know how to generate the "G" column. I am having problems generating the "H" column

Thank you

 

[iggydarsa]

you can use VLOOKUP function

 

[dhx10000]

I have a correction to make, what if columns A and B are reversed? Because that is how my data is. I don't think VLOOKUP will work.

 

[iggydarsa]

That's a good point... I never tried reverse search... Now you made me wonder too...

 

[NateO]

Hello,

See the following:

http://www.mrexcel.com/tip021.shtml

 

[dhx10000]

This is excellent. The last piece of my puzzle is that sometimes my VLOOKUP is returning a 0 in my H column because the H column contains some blanks. Is there any way to tell the VLOOKUP funtion to only return the first non-blank value it comes across, per value in G?

A B
-- --
578
578
578 22995
625
625 36150
735 37555
855 39562


This would in turn look like this:

G H
-- --
578 22995
625 36150
735 37555
855 39562

 

[NateO]

The safest thing to do might be (if it's random, or you might not find a match) would be to make a copy of your data, then delete the blanks in column B, then do your lookup, e.g., delete code:

Sub foo()
Range("b:b").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[dhx10000]

Thank you, this will do just fine