This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Please see the attached file and help me write a formula for what I am looking for?

I need the formula to look within the set of objects in column A on sheet 1 and then find one of those objects in column A on sheet 2 and give me the value from column B on sheet 2 back onto column B of sheet 1.

Thanks!

Test--1.xlsx

I need the formula to look within the set of objects in column A on sheet 1 and then find one of those objects in column A on sheet 2 and give me the value from column B on sheet 2 back onto column B of sheet 1.

Thanks!

Test--1.xlsx

Assuming 1 or 0 matches per row this formula should work for B2 copied down

=IFERROR(LOOKUP(2^15,SEARC

.....but as I say that will give you all zeroes for the data "as is". If I change all the "BO"s to "BX"s I get the results you wanted - see attached

regards, barry

Test--1-barry.xlsx

Assumes consistency in column A of sheet1 based on first entry within string.

Added helper column on sheet2. Have added to left of data and used vlookup, can add further to right and use different function if so required.

Thanks

Rob H

Test--1.xlsx

=SUMPRODUCT(ISNUMBER(SEARC

regards, barry

Can you please explain the parts of this formula? I have been trying to implement it in various other fields but I keep getting a zero value. I mostly don't understand the 2^15 in the Lookup function, but maybe a quick tutorial would help most.

=IFERROR(LOOKUP(2^15,SEARC

The Sumproduct formula worked great for something in the same file I actually needed to use it for!

Thanks!

I updated my file with more data and keep getting zeros unless I change the order of the fields in the search function. As you gave it to me, I get zeros. If I switch them, it appears that I get a result that acts more like a VLookup, but both fields must be equal.

This might be a better example of what I am looking for. I have looked up some of the answers the formula should be giving me, however I am only getting zeros.

Did I screw something up?

Thanks,

Dom

Test--1-barry.xlsx

1) I don't think you were referencing the correct columns.

2) Some of the early values in the "Object" range (in Percentages sheet) are "A"s (or "B"s) so this matches with any "A" in the multi object range - I needed to change the formula so it only matches with the whole part between the commas.

3) Some of the "Object" range values in Percentages sheet have trailing spaces so I used SUBSTITUTE function to remove those.

The resulting formula is as follows:

=IFERROR(LOOKUP(2^15,SEARC

see attached

regards, barry

Test--1-barry2.xlsx

On the attached file, I went to the percentage tab and deleted the letters off the end of the strings that begin with BO01 and BO02. Those now come back as "Missing Data".

How can we get those cells to still show the correct answers in C2 and C3?

I know I keep throwing curveballs at you, but I'm still trying to get it down as simply as my question, to find a string within a larger string and lookup that value in a different tab and give me that value back at the larger string.

I really appreciate your time on this!

Thanks,

Dom

Test--1-barry2.xlsx

regards, barry

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

I virtually had to repeat the formula to check for the first 4 characters if there isn't a full match so that now becomes this:

=IFERROR(IFERROR(LOOKUP(2^

See revised attachment

You also mentioned checking for multiple matches - I suspect that's a step too far for a formula approach unless you start splitting out the comma separated values into individual cells

regards, barry

Test--1-barry3.xlsx