Simple IF variable, but cant seem to figure out how

**ginjack** · 11-24-2009, 08:08 PM

Hello,
I have a 3 lists, one for the data values, whereas other raw data.

Data values-
A1,A2,A3, - (structure)
Red,34,10
Blue,12,52
Green,15,72
Yellow,12,162

Raw Data,
A1,A2,A3
Red
Red
Red
Red
Blue
Red
Green
Yellow
Yellow
Red

Now i want that if cell has "red" in it, then A2 is inserted 34, wheres A3, 10.
I have about 12K entries, and about 300 colors in data values, so pls help as doing it manually... will not be feasible.

I am familiar with running macros, and also with = functions. Any help will be greatly appreciated, Thanks.

**ConneXionLost** · 11-24-2009, 08:14 PM

Hi ginjack,

Seems like all you need to do is use a VLOOKUP function.

Cheers,

**teylyn** · 11-24-2009, 08:16 PM

maybe with a vlookup ...

if your table with the data values lives in A1 to C5, and your raw data starts in A7 you could use

in B7 =VLOOKUP(A7,$A$1:$C$5,2,FALSE)

in C7 =VLOOKUP(A7,$A$1:$C$5,3,FALSE)

**lopiner** · 11-24-2009, 08:21 PM

Equal to teylin suggestion. Here is a file with an example:

**teylyn** · 11-24-2009, 08:24 PM

lopiner, could you please use the forum's file attachment functionality instead of external storage sites? People are a bit wary of those and don't like to get hammered with ads.

**lopiner** · 11-24-2009, 08:28 PM

teylyn,
I'm sorry, I'm new to the forum and didn't realise that there was such functionally. Will use it in the future. Thanks for the notice.

**davesexcel** · 11-24-2009, 08:37 PM

Not knowing what the structure of you workbook looks like, here is a simple example.
Look at Sheet2 for the data for each color would be.

Yay!, This has become my 4,000th post.

**ginjack** · 11-24-2009, 08:49 PM

Thanks for the promt response, and lopiner thanks for uploading the file. It was great help.
Unfortunately, After doing abt 444 entries, it starts to give me a error of #N/A for both row 2 and 3 ?
any ideas why thats going on? I have checked so caps is similar, spelling is similar. I copy pasted the 445th entry to replace the db, but still no luck. Whereas when i replaced the 445th entry with one of the earlier colors, it worked.
Any ideas why this is going on?

**ginjack** · 11-24-2009, 08:56 PM

Problem Solved -
I forgot to change the C4 box. the function was only picking till C4 data bank, so A5, was not being picked at all with it. I cant believe i missed that earlier.

=VLOOKUP(A348,$A$1:$C$345,2,FALSE)
this worked for me since i have 345 rows of colors

Simple IF variable, but cant seem to figure out how

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Simple IF variable, but cant seem to figure out how

Re: Simple IF variable, but cant seem to figure out how

Re: Simple IF variable, but cant seem to figure out how

Re: Simple IF variable, but cant seem to figure out how

Re: Simple IF variable, but cant seem to figure out how

Re: Simple IF variable, but cant seem to figure out how

Re: Simple IF variable, but cant seem to figure out how

Re: Simple IF variable, but cant seem to figure out how

Re: Simple IF variable, but cant seem to figure out how

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions