Vlookup doesnt work here, is there another formula?

[abav]

Hi there,

I have tried a Vlookup however it didn't get the data i wanted, it seemed to be only able to match parts of the data and would bring over incorrect pricing.

Is there another formula that would help here?

So in the attachment on the left you can see the information and pricing that i need to move over to the right pricing column while matching it to incomplete reference data.

So price for 225/55R17 96W is 100, on the system its 225/55R17 W the Vlookup is getting confused here, is there another formula that would work here.

[AliGW]

Administrative Note:

Welcome to the forum.

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

In future, pldease make your title descriptive of what you are trying to do, not what isn't working.

I presume you have tried this?

=VLOOKUP(D2&"*",$A$2:$B$1895,2,0)

[Glenn Kennedy]

I could not find 225/55R17 96W anywhere.

The samples is MASSIVE.... 2000 rows not 10-20 as requested. Also the source data is flawed. There are 3 prices associated with 15565R14 75T. Which one is correct. Please revisit your sample and submit a SMALL sample of 10-20 rows (see yellow banner, top of page) with expected answers.

[abav]

Thanks for that, I will take that into account when posting again!

Yes the Vlookup function was correct in the sense it retrieved data, however the data it took over was incorrect in some instances where it would take price for 13570R15 70T when the lookup was for 14565R15 72S

What i used:
=VLOOKUP(E2:E1515,A:B,2,TRUE)

[AliGW]

Are you addressing me or Glenn?

[Glenn Kennedy]

Please update the sample sheet WITHOUT duplicated entries. 20 rows maximum. If you want answers that work, we need sheets that we can check manually.

[JohnTopley]

There is no way of MATCHING unless you have the EXACT full reference match: and that is not 100% ...... any match will find the first occurrence whether partial or full reference.


21545R17 91W €602.00
21545R17 91W €603.00

[JohnTopley]

"21545R17 *" will return 599 (as you have discovered) vs entries below

21545R17 87V €599.00
21545R17 87V €600.00
21545R17 87Y €601.00
21545R17 91W €602.00
21545R17 91W €603.00

[abav]

Please update the sample sheet WITHOUT duplicated entries. 20 rows maximum. If you want answers that work, we need sheets that we can check manually.

Hi Glenn,

Apologies, please see attachment. Didn't mean to cause confusion. I have included the Vlookup formula in there too.

[AliGW]

@Abav

I asked a question in post #5 and John has also asked questions. Please do not ignore us!

[abav]

Are you addressing me or Glenn?

Post 4 was for you

[AliGW]

OK - and your response to John's questions?

[JohnTopley]

This is a better match BUT does not solve the match when there is no letter appended to the prime reference

=VLOOKUP(LEFT(E2,8) & " **" & RIGHT(E2),$A$2:$B$15,2,0)

[abav]

"21545R17 *" will return 599 (as you have discovered) vs entries below

21545R17 87V €599.00
21545R17 87V €600.00
21545R17 87Y €601.00
21545R17 91W €602.00
21545R17 91W €603.00

Thanks John, that makes sense, is there a formula/modification to vlookup that would take the highest price over?

[JohnTopley]

Try (non-365)

=MAX(IF(LEFT($E2,8) =LEFT($A2:$A2000,8),$B2:$B2000))

Enter with Ctrl+Shift+Enter

As you have 365, use MAXIFS rather than the above. [and 365 only requires normal entry of the above]

You should add a test to check if you have a full reference and use that.

[kisiji]

Dear All,

i hope you will love my work i have made a detail and kindly find the attachment.

[abav]

Try (non-365)

=MAX(IF(LEFT($E2,8) =LEFT($A2:$A2000,8),$B2:$B2000))

Enter with Ctrl+Shift+Enter

As you have 365, use MAXIFS rather than the above. [and 365 only requires normal entry of the above]

You should add a test to check if you have a full reference and use that.

Thanks John, would you be able to put that into the example spreadsheet? It would make sense better for me if I can see the formula working etc.

[abav]

Dear All,

i hope you will love my work i have made a detail and kindly find the attachment.

Hi Kisiji, thanks for looking into this for me. looking at the example you sent, i'm not sure if it taking the pricing into the correct tyre size.

[AliGW]

i hope you will love my work i have made a detail and kindly find the attachment.

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

[JohnTopley]

See attached

[abav]

See attached

On Review i think this might be a solution for me! Thank you for this.

Can I ask, is there a way of putting into the formula that if there is multiple values, it takes the lowest up to the highest?

[abav]

Dear All,

i hope you will love my work i have made a detail and kindly find the attachment.

How do you do that with the spreadsheet where it blanks out the cells that are not in use and just shows the working part of the spreadsheet. It looks really clean and presentable.

[kisiji]

Dear abav
(x), (X) = Column,
-x1-, -X1- = Rows,
1. first i have to inserter (A) to check if there are any doubles or triples in (C)
Formula used in -A2- =C2&"-"&COUNTIF($C2:C$16413,C2)
it does counting of (C) from -C2- till -C16413- only.
formula ends on -C16413- =C16413&"-"&COUNTIF($C$16413:C16413,C16413)

2. then i added (B) so i can add serial number to verify the position of each doubles or triples in (C)
you can even use your own general or custom serial number, best is general so you can navigate to that particular -C- section that's all in Sheet 1,

fun begins in Balance sheet you can name it anything you like but do not forget to change name in each formula

you will only use -C1- only

-A1- blank,
-B1- part code:
-C1- for your use only (you can lock all other cells and can type or even use barcode scanner to fetch data of particular code as well)


-D1- = validates the total entries in Sheet 1 which are double or triple
formula used in -D1- =COUNTIF(D!C2:C16413,BALANCE!C1)


(A)
-A6- 1
Formula used in -A7- =IF(A6>=$D$1,"",A6+1)
Formula used in -A8- =IF(A7>=$D$1,"",A7+1)

(B)
-B6- Onwards
Formula used in -B6- =IFERROR(VLOOKUP($C$1&"-"&A6,D!$A$2:$Z$16413,2,FALSE),"")
Formula used in -B7- =IFERROR(VLOOKUP($C$1&"-"&A7,D!$A$2:$Z$16413,2,FALSE),"")
Drag till where you want to see the results

just repeat this formula for all results and change

=IFERROR(VLOOKUP($C$1&"-"&A6,D!$A$2:$Z$16413,
2, = to the number of Column (X)
FALSE),"")

for example
(A)=1
(B)=2
(C)=3
(D)=4
(E)=5
(F)=6
(G)=7
(H)=8
(I)=9

That's All,
thank you if didn't understood ask i will explain what i can
and if information not relevant kindly please ignore,

[abav]

Thanks for this!

[abav]

Thanks everyone who helped me with this! I have added reputation as a thank you. I will mark this as solved now.