Vlookup, Index or Match?

[cwallace70]

I have a spreadsheet that I am trying to get and exact match on a summary page. The criterias are
if the data in B3 & C3 matches the data on a separate tab but same cell references then the data from cells F3:BR3 should be exact for all for all cells that have the exact same criteria as B3 & C3. To explain myself further. If a cell b3 have a # of 00057 and cell C3 have a nuber of 058 then all cells in those two columns that match those criteria should have the same percentage from column F5:BR2631.

This is an index formula I am trying and I am getting #VALUE! ERROR. Would VLOOKUP be better and can someone assist me with the formula.

Also cross posted here
http://www.mrexcel.com/forum/excel-q...dex-match.html

Thanks

[cwallace70]

=INDEX($B$3&$C$3,MATCH(BASE!B4&BASE!C4,BASE!F4:BR2632,0))

Sorry I forgot to post the formula

[tigeravatar]

cwallace70,

Welcome to the forum!
Attached is an example workbook based on the criteria you described. In Sheet1 cell F3 is this formula:

Please Login or Register  to view this content.

Is something like that what you're looking for?

[cwallace70]

I reviewed the attachment and it's not quite what I am looking to acheive. This is actually pulling the data from one spreadsheet to another. I would like a false if the percentages are an exact match when the criteria for cell B and C are met. I hope I am explaingin myself correctly.

Thanks for your help

[tigeravatar]

cwallace70,

In that case, I would need a sample workbook to see what you are trying to achieve, so...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.

[tigeravatar]

Also, cross posted here:
http://www.mrexcel.com/forum/excel-q...dex-match.html

[Cutter]

Thanks TA

Hello cwallace70, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[cwallace70]

I was unaware of the cross posting rule and I apologize. Thanks for bringing it to my attention.

[cwallace70]

I have attached a sample file for your review. Thanks

[tigeravatar]

cwallace70,

Attached is a modified version of your sample file. In the 'summary' sheet cell D3 and copied down is this formula:

Please Login or Register  to view this content.

That formula will find the row that matches B and C in the Base sheet, and then sum that row from columns F:BR.

[cwallace70]

Thanks for that formula, unfortunately I dont want to sum the amounts in that row. I wouldl iek to change it up so hopefully the formula can be less complicated. I would like to make sure that all if the QRRSS no. of for ex. 0000 and form no. 058 all have the same percentages for just column F, not using the column letter, but using the highlight blue number. Example 1 or 1(E) or 1(Sch). Using that same sample spreadsheet, can you show me a formula that would pick that information and tell me if they match exactly and if it does not match as well.


Thanks

[cwallace70]

I would like get an exact match if two colums match another two columns on a separate tab and determine if the number in the cell for a particular intersection matches exactly

[tigeravatar]

how does the formula know which intersection to use? What number is the number in that intesection being compared against?

[cwallace70]

the number in the intersection is not being compared against anything.
If 0000 and 058 in column B & C matches 0000 & 058 on the "Base" tab in columns B & C then look at E2 (this is where the intersection number is located) and tell me if the percentages in column "E" are all the same for 0000 & 058. If it is TRUE if not FALSE.

I've attached the spreadsheet again showing the blue highlighted intersections. I guess I could probably just use E2 instead of the number in that cell of "1"

Thanks

[tigeravatar]

cwallace70,

I have no idea what you mean by

look at E2 (this is where the intersection number is located) and tell me if the percentages in column "E"...

There are no numbers (percentages or otherwise) in column E. Column E are text values. So i instead took your intent as the following:
Find the row in sheet 'Base' where column B and C match the column B and C entries in sheet 'summary'.
Find if all the numbers in sheet 'Base' columns F:BR for that row are identical
If they are, return TRUE
If they are not, return FALSE

That is a rather complex request, so the formula is rather large, but here it is. In sheet 'summary' cell D3 and copied down:

Please Login or Register  to view this content.

As a side note, the formula could probably be shortened if you used a helper column.

[cwallace70]

If I changed the formula to only include column F would this make sense within this formula? Or would one of the Index formula need to point to F2 where the inserction number is? I hope this makes sense.

=(COUNTIF(INDEX(Base!F:F,MATCH(1,INDEX((Base!$B$3:$B$2631=$B3)*(Base!$C$3:$C$2631=$C3),),0),0),"<>"&INDEX(INDEX(Base!F:F,MATCH(1,INDEX((Base!$B$3:$B$2622=$B3)*(Base!$C$3:$C$2622=$C3),),0),0),MATCH(TRUE,INDEX(INDEX(Base!F:F,MATCH(1,INDEX((Base!$B$3:$B$2622=$B3)*(Base!$C$3:$C$2622=$C3),),0),0)<>"",),0)))-COUNTBLANK(INDEX(Base!F:F,MATCH(1,INDEX((Base!$B$3:$B$2622=$B3)*(Base!$C$3:$C$2622=$C3),),0),0))=0)

[tigeravatar]

That will only look at column F in the matching row. And the row its looking at will be off because the formula is finding a match starting at row 3, not row 1.

Can you please provide a detailed explanation of what you're trying to accomlish as if you were to do it by hand. So the step-by-step logical process that you would go through manually to get the result? Also, the example workbook you keep posting doesn't have the results you want. It just has a couple of formulas that result in errors. Seeing the desired results (typed in manually) would be a big help also.

[cwallace70]

how does the formula know which intersection to use? What number is the number in that intesection being compared against?

Going back to your question above. I just realized I did not out the intersections on the summary sheet. this is what it needs to be caompared to

[cwallace70]

That will only look at column F in the matching row. And the row its looking at will be off because the formula is finding a match starting at row 3, not row 1.

Can you please provide a detailed explanation of what you're trying to accomlish as if you were to do it by hand. So the step-by-step logical process that you would go through manually to get the result? Also, the example workbook you keep posting doesn't have the results you want. It just has a couple of formulas that result in errors. Seeing the desired results (typed in manually) would be a big help also.

I am trying to accomplish finding if all the percentages match if a QRRSS number is 00000 and a from number is 058 for example. Meaning if those two numbers appear four times in column B and C then the percentages that is in the intersection should all be an exact match. The intersection would be The highlighted blue number (F2). whereas I could just drag the formula down and across and the intersection and column changes within the formula.

[tigeravatar]

alright, so...

1) Find all rows in sheet 'Base' where columns B and C match columns B and C in sheet 'summary'
2) Test if all columns F:BR for all of those found rows in sheet 'Base' are identical
3) If they are all identical return TRUE else FALSE

Based on that, there are 4 rows found for 00000 and 064 in sheet 'Base'. Those 4 rows do not have identical information in column F:BR so the result should be FALSE. I say they do not have identical information because:

The found rows are 4, 659, 1314, 1969
Row 4 has: column BA ">11" = 2.00%
Row 659 has: column BA ">11" = 2.00%
Row 1314 has: column AB "2-10" = 5.00%
Row 1969 has: column Z "2-6" = 5.00%

So not only are the populated columns different, but the values are also different.

Is that correct?

[cwallace70]

Below shows that 0000 & 058 apear 4 times on the base sheet and in row 1 the percentages are different so the results from the formula for 00000 & 058 should be false for what ever row the different oercentage falls in and in row 2 all the %'s are the same so everytime it hits that row it would come back as true because all the percentages match. I hope this explains what I am trying to do. I fit can't be done then it would have to be a manual checking process.

[tigeravatar]

???? You are continually giving me more questions. In this data set, the left-hand side (summary sheet) 00000 058 only appears three times. The 4th appearance of 00000 058 on the right-hand side (Base sheet) is lined up with 214XX 086. Also, you have 20% 20% for the first three occurrences and 15% 20% for the 4th occurrence. Where does the 20/20/20/15 come from? In the sample sheets you provided earlier (including the most recent one), the summary sheet had no percentages at all in columns F:BR.

As for whether or not this can be done, I assure whatever you're trying to automate is possible, I just still don't know what it is exactly that you're trying to automate...

[cwallace70]

I understand the confusion and I apologize and thanks for being patient with me.
The summary sheet will not have any percentages because that is where the formula will be going. I am trying to use the summary sheet to look at the base sheet and tell me that 0000 and 058 all have the exact percentage number in the column they intesect in.
On the base sheet there is column F with a number in F1 of (1).The formula should look at 0000 & 058 on the summary sheet & match it to the same numbers on the Base sheet and then look at the number in F1 and all the percentages in column F should be the same for 0000 & 058.
if a percentage number is different then give me a False if its the same then give me TRUE.

[tigeravatar]

cwallace70,

Attached is version 3. In sheet 'summary' cell E3 and copied over and down to BV30 is this formula:

Please Login or Register  to view this content.

That formula checks if there are any entries in any of the rows in sheet 'Base' in which column B and C match columns B and C from sheet 'summary'. If there is an entry, it checks if all entries for that column are identical. If they are it returns TRUE else FALSE.

The only times the formula returns TRUE are for 00099 103, 00099 104, and 00099 207 for column "1". Everything else is blank (no entries at all) or FALSE (entries found, but not all were identical). Is that what you're looking for?

[cwallace70]

That is exactly what I am trying to do. I will try out the formula and let you know if I have acheived the results I need..
Thanks for all your help. I will reply the results soon.

[cwallace70]

That formula is exactly what I needed. It worked perfectly.

Thanks for al lyour help and Patience! Your Great. I am going to study this formula and learn it.

[tigeravatar]

You're very welcome

If that takes care of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save