Hi there
- am baffled by my index match match match formula only returning correct values in the first 7 columns (Sht 'Table-Transactions' Q7:X7)
- data is examined/extracted from Sht'InputOutsideLines'
- looking at cell X7 (Table-Trans...) have evaluated each match which produces the correct figures (ie. 1 ,3, 2 ) & value of 8 should result but cell remains blank !
- what am I missing ?
cheers
Chris
Last edited by Dorado99; 10-31-2021 at 04:00 AM.
You have 3 paramters for INDEX/MATCH but the third is ignored: you get the results for row 2 values equal to 1 by "default"
Try
in Q7
=IFERROR(INDEX(InputOutsideLines!$T$19:$AB$42,MATCH($H$7,InputOutsideLines!$T$19:$T$42,0)+MATCH(Q2,InputOutsideLines!$U$19:$U$42,0)-1,MATCH(Q1,InputOutsideLines!$T$18:$AB$18,0)),"").
Last edited by JohnTopley; 10-31-2021 at 05:01 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thankyou John for quick response - that is vast improvement - but not quite there.
New formula now recognises the values in column "U" although it ignores values if 'Bait Station' number already 'used'.
New formula doesn't seems to evaluate the 1st 'Match' ie. cell H7 - the Trap Line # in col. T of the source data.
Would you be able to look at it again please?
Many thanks
Chris
Are you looking for this result?
Q7=IFERROR(INDEX(InputOutsideLines!$T$19:$AB$42,MATCH($H$7,InputOutsideLines!$T$19:$T$42,0)-1+Q$2,MATCH(Q1,InputOutsideLines!$T$18:$AB$18,0)),"")
Maybe try
=VLOOKUP(Q$8,InputOutsideLines!$S$19:$AB$38,MATCH(Q$1,InputOutsideLines!$S$18:$AB$18,),)
Hi Raul
Not quite there - formula is creating Bait Station numbers which aren't in the data schedule i.e. 5
Seems to allocate as Bait station # the row number in data schedule
Also 1st Match (cell H7) not being recognised - data from table should only extract from column T that matched cell H7 (i.e. "1n")
cheers
Chris
I don't get your data and neither your explanations, I'm afraid.
"formula is creating Bait Station numbers which aren't in the data schedule i.e. 5" - what does this even mean?
The formuala creates "1n1" numbers: 2,3,4,5,7.
Thereafter 1n2 numbers and so on.
What exactly are your desired results?
For me the spreadsheet suffers from a lack of clarity and very confusing formulas.
e.g.: Line.Bs. on InputOutside Lines are 1n1, 1n2 and 1n4 etc.
However, on Table-Transactions: while you have a running nummer 1,2 and 3 in row 2, row 8 shows 1n1, 1n2 and 1n3 (which doesn't exist) - this is just inconsistent and confusing to me.
Can you post an updated sample workbook?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi Bo
That works a treat - you're a genius - thanks soooo much!
cheers
Chris
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks