Vlookup or If statement Help

[JPriest]

Hi all,

I'm hoping you can help ease the work of my CTRL+COPY, CTRL+PASTE fingers.

I have one worksheet that which I have to fill-in with data from the web. I have copied and cleaned the data into 2 columns in a separate worksheet. I would like to search worksheet #2, col B for data that matches workbook#1, col b, and insert the correlating data from worksheet#2 col A into workbook#1 col e.

Right now I'm coping and pasting these words into a search function on the webpage and then coping the data from the webpage into the workbook and my fingers are hurtin!

Thanks for your help in advance,
Jeff

[MrShorty]

Not knowing all the specifics, it's a little difficult to be sure it will work, but VLOOKUP should be able to do this for you.

1) Sheet#2 column C = column A
2) In sheet#1 column E =VLOOKUP(B1,sheet2!b1:c100,2,false)

Adapt references as necessary. Note that VLOOKUP can only return one value. If the values in column B occur more than once in the lookup table, you will always return the first incidence. Also I've set the final argument to FALSE because I don't know if you can sort the lookup table or if you otherwise will want to lookup using other than an exact match.

Does that make sense or help at all?

[JPriest]

Mr.Shorty,

It makes sense, however when I make the changes, I get a #REF!. Sheet 1, Col A contains a constant text and Sheet1 Col B contains the Code. I am using Vlookup to search Sheet2 ColA for the constant from Sheet1 Col A and extracting the correlating data from Sheet2 ColB into Sheet1 ColB.

Here is the formula I'm currently using:
=VLOOKUP(A1,Sheet2!A1:A100,2,FALSE)

As a side note, when I change the col Index from 2 to 1 I get the value of Sheet2 Col A in Sheet1 Col B.

Hope you can help me out with this,
Jeff

[greg7468]

Hi,
I think you need to change your range to A1:B100

HTH.

[JPriest]

Greg,
I made your suggested changes and now I get an #N/A error!

Any thoughts?

Jeff

[MrShorty]

From Excel (2002) help

If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

Do either of these conditions apply?

[JPriest]

When I select "Help on this error' and then 'step in', i get the message that 'cell currently being evaluated contains a constant'.

What I'm trying to do is automate a web report after I get the information from a web query. I want to automatically scan the web query for a specific web link in one col and place the correlating hits from the cell beside it into the report.

the formula i have been trying is the one above, but to no avail.

Please help!!

Jeff

[NaturalLogZero]

3 1/2 minute demo on LOOKUP and avoiding some common problems in returning state from zip code
http://www.kbdpower.com/Newsletter/public/lookup.html

Hi all,

I'm hoping you can help ease the work of my CTRL+COPY, CTRL+PASTE fingers.

I have one worksheet that which I have to fill-in with data from the web. I have copied and cleaned the data into 2 columns in a separate worksheet. I would like to search worksheet #2, col B for data that matches workbook#1, col b, and insert the correlating data from worksheet#2 col A into workbook#1 col e.

Right now I'm coping and pasting these words into a search function on the webpage and then coping the data from the webpage into the workbook and my fingers are hurtin!

Thanks for your help in advance,
Jeff