Cell look up 1

[bnsSteve]

Hello,

Wondering if its possible to create a formula that will take a cell in one sheet and reference a column in another sheet then return a value in that row if the cells match.

For example:
In Sheet A - cell A1 will take the value in B2 then check all the values in Column A in Sheet B to see if there's a match to Cell A1 in Sheet A. If yes, Cell A1(sheet A) will return the value that's in the next column(Column B - sheet B) that matched the cell in Column A (sheet B)

I hope this makes sense and is doable. Please let me know if further clarification is required.

Thanks.
bnssteve

Running Crystal 11 - 14.0.2.364

 

[judgeh59]

if understand what you are trying to do, you can use IFERROR and VLOOKUP together and do what you would like to do...another way is INDEX and MATCH (I believe that's correct, Skip V is the INDEX and MATCH king)...try them both...HTH

Be Alert, America needs more lerts

 

[bnsSteve]

Thanks for the reply judgeh59. Would you know how to write this? If not, hopefully Skip V will come to the rescue!

Should also not that I'm using excel 2010.

Thanks.
bnsSteve.

Running Crystal 11 - 14.0.2.364

 

[SkipVought]

Hi,
[tt]
=index(sheetB!c:c,match(b1,sheetB!b:b,0),1)
[/tt]
If I understand correctly.

Using 2010, you should need to do VERY LITTLE TYPING after you ENTER the EQUAL sign, CHOOSING , rather from the pop ups and POINTING with your mouse to the references.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bnsSteve]

Hi Skip,

I couldn't quite get it, it's probably because I have the rows and columns not correctly identified in the formula but I made a quick example that will hopefully clarify it better in case the formula isn't what I'm trying to achieve.

In Sheet 1:
A1=(blank) B1=123
A2=(blank) B2=456
A3=(blank) B3=789

In Sheet 2:
A1=123 B1=ABC
A2=654 B2=DEF
A3=789 B3=GHI

In short, on sheet1:A1 should return the value in sheet2:B1 because sheet1:B2 matches sheet2:A1 and sheet1:A3 should return the value in sheet2:B3 because sheet1:B2 matches sheet2:A1 and sheet2:A2 shouldn't return anything as sheet1:B2 doesn't match anything in sheet2:columnA

Thanks again for all your help Skip!








Running Crystal 11 - 14.0.2.364

 

[SkipVought]

Then index should return from column A not C

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bnsSteve]

Keeps giving me an error that that says 'value is not available to the formula or function'

Changed the formula to this "=INDEX(sheetB!A:A,MATCH(B1,sheetB!B:B,0),1)" but returns #N/A.

Running Crystal 11 - 14.0.2.364

 

[SkipVought]

Is your sheet name ACTUALLY sheetB, no SPACES?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bnsSteve]

Yep

Running Crystal 11 - 14.0.2.364

 

[SkipVought]

oops,
[tt]
=INDEX(SheetB!B:B,MATCH(B1,SheetB!A:A,0),1)
[/tt]
I almost always use TABLE structures as lookup tables and Named Ranges, Makes seeing the errors much easier!!!

Sorry.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bnsSteve]

That worked! That's going to become real handy.

Thanks Skip!

Running Crystal 11 - 14.0.2.364

 

[SkipVought]

Here's an example of what I was referring to on SheetB
[tt]
CompanyID Company
123 ABC
654 DEF
789 GHI
[/tt]
and here's the formula using named ranges
[tt]
=INDEX(Company,MATCH(B2,CompanyID,0),1)
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bnsSteve]

Actually that seems a lot cleaner and simpler. Thanks for that!

Running Crystal 11 - 14.0.2.364