Index Match with Left function?

[g17]

hoping someone might be able to help?

trying to copy a value from one sheet to another with the following criteria:

-if the first 6 numbers of the SKU on Sheet 1 matches the SKU on Sheet 2, copy the Price on Sheet 2 to Sheet 1. I used the following formula but it returns an N/A value.

=INDEX('Sheet 2'!B2:B3, MATCH(LEFT(B2,6),'Sheet 2'!A2:A3,0),1)

Sheet 1

SKU                          Price
12345678901                 (Formula goes here)
23456789012         


Sheet 2

SKU                          Price
123456                       $9.98
234567                       $34.98

tnx!
-g

 

[pagrender]

Does this work for you:
=INDEX(Sheet2!B2:B3, MATCH(LEFT(A2,6)*1,Sheet2!A2:A3,0),1)

It returns $9.98 in my test file.

 

[MrKowz]

Welcome to the forums!

The problem with using a string function on numbers to try to compare with other numbers is that a formatting issue arises. You either have to compare a string with a string, or numbers with numbers.

To fix your issue, you can use:
=INDEX('Sheet 2'!B2:B3, MATCH(VALUE(LEFT(B2,6)),'Sheet 2'!A2:A3,0),1)

or

=INDEX('Sheet 2'!B2:B3, MATCH(LEFT(B2,6)*1,'Sheet 2'!A2:A3,0),1)

 

[T. Valko]

hoping someone might be able to help?

trying to copy a value from one sheet to another with the following criteria:

-if the first 6 numbers of the SKU on Sheet 1 matches the SKU on Sheet 2, copy the Price on Sheet 2 to Sheet 1. I used the following formula but it returns an N/A value.

=INDEX('Sheet 2'!B2:B3, MATCH(LEFT(B2,6),'Sheet 2'!A2:A3,0),1)

Sheet 1
 
SKU                          Price
12345678901                 (Formula goes here)
23456789012         
 
 
Sheet 2
 
SKU                          Price
123456                       $9.98
234567                       $34.98

tnx!
-g

Try it like this...

=INDEX('Sheet 2'!B$2:B$3,MATCH(--LEFT(B2,6),'Sheet 2'!A$2:A$3,0))

Copy down as needed.

 

[Aladin Akyurek]

1]

C2, Sheet 1

=INDEX('Sheet 2'!$B$2:$B$3, MATCH(LEFT(B2,6)+0,'Sheet 2'!$A$2:$A$3,0))

2]

C2, Sheet 1:

=LOOKUP(9.99999999999999E+307,FIND('Sheet 2'!$A$2:$A$3,LEFT(B2,6)),'Sheet 2'!$B$2:$B$3)

 

[g17]

this one works for me:

=INDEX(Sheet2!B2:B3, MATCH(LEFT(A2,6)*1,Sheet2!A2:A3,0),1)

thanks everyone for the super speedy replies! u guys r awesome. =)